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;