Skip to main content

SQL Basic Notes

 SQL

Data -data can be facts related to any objects in consideration.

Eg- age, video, other data, tweets, messages, posts, photos


Database -database is systematic collection of data which support storage and manipulation of data in easy way.

Eg- telephone directory, power generation, contacts in our phone, social media


Why we need a database?

Managing large data is a hassle.

Accuracy of the data. Difficult to track accuracy in spreadsheet.

Ease of data upload.

Data security.

Data Integrity.


Introduction to DBMS

Collection of programs that enables enables its users to access  database, manipulate data, reporting/ representation of data.


SQL -one of the standard language for dealing with relational databases which can be used to create, read, update and delete database records.


What is a relational database?

A relational database is a type of database that stores and  provides access to data points that are related to one another.

Data organised in tables. We map data so that database knows what’s important and what’s not. We do this using keys which provides relation between one column of data and other column of data.

Eg- MySQL, ORACLE, SQL Server, SYBASE


Query- actual code

Eg- SELECT * FROM data1 WHERE Age<20


Applications of SQL

-Can be used as DDL

-can be used as DML

-can be used as DCL

-can be used as client/server language

-three-tier architecture


Basics of SQL Tables

-A table is a database object which is comprised of rows and columns in sql

-collection of related data held in a table format


Fields- column

Eg- salary


Record- individual entry that exits in table


Numeric data, char data, date data


Bigint

int 

smallint

tinyint

decimal


Character

char

varchar

text



Date type

data

time

year


SQL Server 2017

Developer edition

Custom 

Standalone installtion

Select Database engine services

Microsoft sql server management studio


Commands-


CREATE DATABASE databasename;

CREATE DATABASE happy;


USE database name;

USE DATABASE happy;


DROP DATABASE databasename;

DROP DATABASE igneous;


Constraints are used to specify rules for data in table

Not Null

Default

Unique

Primary Key


name the table->define the column->assign datatypes to each column

CREATE TABLE tablename(

column1 datatype,

column2 datatype,

…..

columnN datatype,

PRIMARY KEY(column_x)

);


Table can’t have more than one primary key.


CREATE TABLE employee(

e_id int not null,

e_name varchar(20);

e_salary int,

e_age int,

e_gender varchar(20),

e_dept varchar(20),

PRIMARY KEY(e_id)

);


INSERT INTO table name 

VALUES(val1,val2…..);


INSERT INTO employee values(

1, ’Sam’, 95000, ’Male’, ‘Operations’

);


INSERT INTO employee values(

2’, Bob’, 80000, ’Male’, ’Support’

);


SELECT column1,…

FROM tablename;


SELECT e_name FROM employee;


SELECT e_name, e_gender, e_salary

FROM employee;


SELECT * FROM employee;


SELECT DISTINCT column1,…

FROM table name;


SELECT DISTINCT e_gender

FROM employee;


WHERE clause 


SELECT col1,…

FROM table name

WHERE condition;


SELECT * 

FROM employee

WHERE e_gender=‘Female’;


SELECT *

FROM employee

WHERE e_age<30;


SELECT *

FROM employee

WHERE e_salary>100000;


Multiple conditions- AND, OR, NOT


SELECT col1,…

FROM table name

WHERE condition1 AND condition2 AND condition3…..;


SELECT *

FROM employee

WHERE e_gender=‘Male’ AND e_age<30;


SELECT *

FROM employee

WHERE e_dept=‘Operations’ AND e_salary>100000;


SELECT col1,…

FROM table name

WHERE condition1 OR condition2 OR condition3…..;


SELECT *

FROM employee

WHERE e_dept=‘Operations’  OR e_dept=‘Analytics’;


SELECT *

FROM employee

WHERE e_salary>100000 OR e_age>30;


SELECT col1,…

FROM table name

WHERE NOT condition1;


SELECT *

FROM employee

WHERE NOT e_gender=‘Female’;


SELECT *

FROM employee

WHERE NOT e_age<30


LIKE is always used in conjunction with wild card characters

Wild card characters-

% represents 0,1 or multiple characters

_ represents a single character


SELECT col_list

FROM tablename

WHERE columnN LIKE ‘_XXXX%’;


Pattern should always be inside single quotes.


SELECT *

FROM employee

WHERE e_name LIKE ‘J%’;


SELECT *

FROM employee

WHERE e_age LIKE ‘3_’;


Between


SELECT col_list

FROM tablename

WHERE columnN BETWEEN val1 AND val2;


SELECT *

FROM employee

WHERE e_age BETWEEN 25 AND 35;


SELECT *

FROM employee

WHERE e_salary BETWEEN 90000 AND 120000;


Basic functions in sql-

MIN()

MAX()

COUNT()

SUM()

AVG()


SELECT MIN(columnN) 

FROM tablename;


SELECT MIN(e_age)

FROM employee;


SELECT MIN(e_salary)

FROM employee;


SELECT MAX(columnN)

FROM tablename;


SELECT MAX(e_age)

FROM employee;


SELECT MAX(e_salary)

FROM employee;


SELECT COUNT(*)

FROM tablename

WHERE condition;


SELECT COUNT(*)

FROM employee

WHERE e_gender=‘Male’;


SELECT SUM(columnN)

FROM tablename;


SELECT SUM(e_salary)

FROM employee;


SELECT AVG(columnN)

FROM tablename;


SEELCT AVG(e_age)

FROM employees;

Comments

Post a Comment

Popular posts from this blog

ML Foundations Course by Great Learning- Notes

In this blog, I am going to post my notes, assignments etc that I did during my course on ML Foundations by Great Learning. ML Maths Basics Topics Covered Line Concept Line, Planes and Hyper planes Vector Algebra-magnitude and dimension Vector Algebra-vector operations Dot Product Matrix Algebra Functions Maxima and Minima of Functions Chain Rule Maxima and Minima Applications in ML Gradient Descent using Partial Derivatives Intro to AI and ML AI-computer program that does something smart or makes smart decisions When computer program learns about the world from data we call it ML. We assume past is a good representation of future. Model building from data take data as input find patterns in data summarise the pattern in a mathematically precise way Machine Learning automates this model building. If data is without noise then finding a pattern is easy but unfortunately data contains both data and noise.  Noise is unstructured and random. It does not repeat itself. ML does not assum...
Post a Comment
Read more

Password Store App in C

A simple password storage type application developed using C language. Users can register and then login using their account. Password, Website name, Email Id can be stored for different accounts. It has a menu based interface to perform user operations like addition, modification, deletion and others. Users need to remember just one password, using which he can access many others of different accounts. Also many users can use same app. Data of each user will created and stored in different .DAT files which will be created when user registers first time. Structures, file handling, pointers etc are used. Code Link
Post a Comment
Read more

OCR Image Text Detection and Image Manipulation Project

Developed as a course project, the main goal behind this project was to test ability to learn and use python libraries , use openCV to detect faces, tesseract to do optical character recognition and ability to use PIL to composite images together into contact sheets.  Task was to write python code which allows one to search through the images looking for occurrences of keywords and faces, to perform text detection on newspaper images data and  r eturn a contact sheet of all the faces which were located on the newspaper page which mentions that text . I divided whole task into subtasks into functions like, get files, binarise, to check is string is found, to chow faces, to show sheet,  building contact sheet and used libraries to achieve each task, like for images used PIL, cv2, etc 
Post a Comment
Read more