Learn SQL in 20 days

DAY 1

SQL ( STRUCTURED QUERY LANGUAGE ) 

 DATABASE 

What is DATA ?

 "Data is a raw-fact which describes the attributes of an Entity ".

 

Properties or Attributes

DATABASE : "Database is a place or a medium in which we store the data in a Systematic and organized manner "

 The basic operations that can be performed on a database are

▪ CREATE / INSERT

▪ READ / RETRIEVE

▪ UPDATE / MODIFY

▪ DELETE / DROP

These operations are referred as "CRUD" Operations .

DATABASE MANAGEMENT SYSTEM ( DBMS ) :

"It is a software which is used to maintain and manage The database "

▪ Security and authorization are the two important features that DBMS provides .

▪ We use query language to communicate or interact with DBMS

▪ DBMS stores the data in the form of  files .

RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS ):

"It is a type of DBMS software in which we store the data. In the form of Tables ( rows & columns ) ".

• We use SQL to communicate or interact with RDBMS

• RDBMS stores the data in the form of Tables.

RELATIONAL MODEL :

Relational Model was designed by E.F CODD .

In Relational Model we can store the data in the from of tables .

Any DBMS which follows Relational Model becomes RDBMS .

Any DBMS which follows rules of EF CODD becomes RDBMS .

TABLE : "It is a logical organization of data which consists of Columns &Rows .

RULES OF E.F CODD :

1. The data entered into a cell must always be a single valued data . 

2. According to E.F CODD we can store the data in Multiple Tables , If needed we can establish a connection between the tables with the Help of Key Attribute .

3. In RDBMS we store everything in the from of tables including 

Metadata .

Example : Metadata : The details about a data is knows as Metadata.

4. The data entered into the table can be validated in 2 steps .

• By assigning Datatypes .
• By assigning Constraints .

Datatypes are mandatory , whereas Constraints are Optional .

    DATATYPES :

      It is used to specify or determine the type of data that will be stored In a particular memory location .

      Datatypes in SQL :

1. 1. CHAR

2. 2. VARCHAR / VARCHAR2

3. 3. DATE

4. 4. NUMBER

5. 5. LARGE OBJECTS

i. i. Character Large Object .

ii. ii. Binary Large Object .

       NOTE : SQL is not a Case Sensitive Language .

    

1. CHAR : In character datatype we can store 'A-Z' , 'a-z' , '0-9' 

                    And Special Characters( $ , & , @ , ! … ) .

• Characters must always be enclosed within single quotes ' '.
• Whenever we use char datatype we must mention size
• Size : it is used to specify number of characters it can store .

1. The maximum number of characters it can store is 2000ch.

• Char follows fixed length memory allocation .

                       Syntax: CHAR ( SIZE )

2.VARCHAR : In varchar datatype we can store 'A-Z' , 'a-z' , '0-9'

                               And Special Characters( $ , & , @ , ! … ) .

• Characters must always be enclosed within single quotes ' '.
• Whenever we use char datatype we must mention size
• Size : it is used to specify number of characters it can store .

1. The maximum number of characters it can store is 2000ch.

• VarChar follows variable length memory allocation .

                                      Syntax: VARCHAR ( SIZE )

Example : VARCHAR ( 8 )

NOTE : VARCHAR2 : it is an updated version of varchar where in We can store up to 4000Ch.

                                             Syntax: VARCHAR2( SIZE )

3. NUMBER : It is used to store numeric values .

               SYNTAX: NUMBER ( Precision , [ Scale ] )

[ ] - Not Mandatory .

Precision : it is used to determine the number of digits used To store integer value .

Scale : it is used to determine the number of digits used to store Decimal ( floating ) value within the precision .

➢ Scale is not mandatory , and the default value of scale Is zero ( 0 ) .

4. DATE : it is used to store dates in a particular format . It used Oracle specified Format.

Example:

DOB			Hire date			Anniversary
Date			Date			Date

        '01-JAN-1945’ ‘20-JUN-20'  '15-APR-2008'

5. LARGE OBJECTS

1. Character large object (CLOB):

It is used to store characters up to 4 GB of size.

2.Binary large object (BLOB):

It is used to store binary values of images, mp3, mp4 Documents etc. …. Up to 4GB of size.

CONSTRAINTS:

It is a rule given to a column for validation.

Types of Constraints:

1. UNIQUE

2. NOT NULL

3. CHECK

4. PRIMARY KEY

5. FOREIGN KEY.

1. UNIQUE: "It is used to avoid duplicate values into the column ".

2. NOT NULL: "It is used to avoid Null ".

3. CHECK: "It is an extra validation with a condition 

If the condition is satisfied, then the value is accepted else Rejected ".

4. PRIMARY KEY: "It is a constraint which is used to identify a record Uniquely from the table " 

Characteristics of Primary key:

➢ We can have only 1 PK in a table

➢ PK cannot accept duplicate / repeated values.

➢ PK cannot accept Null

➢ PK is always a combination of Unique and Not Null Constraint.

5. FOREIGN KEY: "It is used to establish a connection between the the tables "

Characteristics of Foreign key:

➢ We can have only Multiple FK in a table

➢ FK can accept duplicate / repeated values.

➢ FK can accept Null

➢ FK is not a combination of Unique and Not Null Constraint.

➢ For an Attribute (column) to become a FK, it is mandatory That it must be a PK in its own table .

Example:

Primary key Check ( Salary >  Check

Differentiate between Primary key and Foreign key . 

	PRIMARY KEY			FOREIGN KEY
	It is used to identify a records			It is used to establish a connection
	Uniquely from the table.			Between the tables
	It cannot accept Null			It can accept Null
	It cannot accept duplicate values			It can accept duplicate values
	It is always a combination of			It is not a combination of
	Not Null and Unique constraint			Not Null and Unique constraint
	We can have only 1 PK in a table			We can have Multiple FK in a table

NOTE : NULL

Null Is a keyword which is used to represent Nothing / Empty Cell.

Characteristics of Null :

➢ Null doesn’t represent 0 or Space .

➢ Any operations performed on a Null will result in Null itself

➢ Null doesn’t Occupy any Memory .

➢ We cannot Equate Null .

OVERVIEW OF SQL STATEMENTS :

1. DATA DEFINITION LANGUAGE  ( DDL )

2. DATA MANIPULATION LANGUAGE  ( DML )

3. TRANSCATION CONTROL LANGUAGE  ( TCL )

4. DATA CONTROL LANGUAGE  ( DCL )

5. DATA QUERY LANGUAGE ( DQL )

DATA QUERY LANGUAGE ( DQL ) :

"DQL is used to retrieve the data from the database " . It had 4 statements :

1. SELECT

2. PROJECTION

3. SELECTION

4. JOIN

1. SELECT : "It is used to retrieve the data from the table and display it.

2. PROJECTION : "It is a process of retrieving the data by selecting only the columns is known as Projection " 

➢ In projection all the records / values present in a particular column are by default selected .

3.SELECTION : "It is a process of retrieving the data by selecting both the columns and rows is known as Selection " .

4.JOIN :"It is a process of retrieving the data from Multiple tables simultaneously is known as Join " .

                                                          PROJECTION

➢ "It is a process of retrieving the data by selecting only the columns is known as Projection " .

➢ In projection all the records / values present in a particular column are by default selected .

SYNTAX : 

SELECT * / [DISTINCT] Column_Name / Expression [ALIAS] FROM Table_Name ;

ORDER OF EXECUTION

1. FROM Clause

2. SELECT Clause

Example : Write a query to display names of all the students .

NOTE :

➢ FROM Clause starts the execution .

➢ For FROM Clause we can pass Table_Name as an argument .

➢ The job of FROM Clause is to go to the Database and search for the table and put the table under execution .

➢ SELECT Clause will execute after the execution of FROM Clause

➢ For SELECT Clause we pass 3 arguments

• *
• Column_Name
• Expression

➢ The job of SELECT Clause is to go the table under execution and select the columns mentioned .

➢ SELECT Clause is responsible for preparing the result table .

➢ Asterisk(*) : it means to select all the columns from the table .

➢ Semicolon : it means end of the query .

➢ WAQTD student id and student names for all the students.

SELECT SID , SNAME FROM STUDENT ;

➢ WAQTD name and branch of all the students .

SELECT SNAME , BRANCH FROM STUDENT ;

➢ WAQTD NAME , BRANCH AND PERCENTAGE FOR ALL THE STUDENTS .

SELECT SNAME , BRANCH , PER FROM STUDENT ;

➢ WAQTD details of all the students from students table .

 SELECT *

FROM STUDENT ;

➢ WAQTD sname , sid , per , branch of all the students .

SELECT SNAME , SID , PER , BRANCH FROM STUDENT ;

EMP Table :

WAQTD name salary and commission given to all the employees .

Select ename , sal , comm From emp ;

➢ WAQTD name of the employee along with their date of joining .

Select ename , hiredate From emp ;

➢ WAQTD dname and location for all the depts .

Select dname , loc From dept ;

QUESTIONS ON EMP AND DEPT TABLE:

1.WRITE A QUERY TO DISPLAY ALL THE DETAILS FROM THE EMPLOYEE TABLE.

2.WAQTD NAMES OF ALL THE EMPLOYEES.

3.WAQTD NAME AND SALARY GIVEN TO ALL THE EMPLOYEES.

4.WAQTD NAME AND COMMISSION GIVEN TO ALL THE EMPLOYEES.

5.WAQTD EMPLOYEE ID AND DEPARTMENT NUMBER OF ALL THE EMPLOYEES

IN EMP TABLE.

6.WAQTD ENAME AND HIREDATE OF ALL THE EMPLOYEES .

7.WAQTD NAME AND DESIGNATION OF ALL THE EMPLPOYEES .

8.WAQTD NAME , JOB AND SALARY GIVEN ALL THE EMPLOYEES.

9.WAQTD DNAMES PRESENT IN DEPARTMENT TABLE.

10.WAQTD DNAME AND LOCATION PRESENT IN DEPT TABLE.

DISTINCT Clause

"It is used to remove the duplicate or repeated values from the Result table " .

• Distinct clause has to be used As the first argument to select clause .
• We can use multiple columns As an argument to distinct clause, it will remove the combination of columns in which the records are duplicated .

Example:

SELECT SNAME FROM STUDENT;

➢ SELECT DISTINCT SNAME FROM STUDENT ;

➢ SELECT DISTINCT BRANCH FROM STUDENT ;

➢ SELECT DISTINCT PER FROM STUDENT ;

➢ SELECT DISTINCT BRANCH , PER FROM STUDENT;

OVERVIEW OF SQL STATEMENTS :

1. DATA DEFINITION LANGUAGE  ( DDL )

2. DATA MANIPULATION LANGUAGE  ( DML )

3. TRANSCATION CONTROL LANGUAGE  ( TCL )

4. DATA CONTROL LANGUAGE  ( DCL )

5. DATA QUERY LANGUAGE ( DQL )

DATA QUERY LANGUAGE ( DQL _) :

"DQL is used to retrieve the data from the database " .

 It had 4 statements :

1. SELECT

2. PROJECTION

3. SELECTION

4. JOIN

1. SELECT : "It is used to retrieve the data from the table and display it.

2. PROJECTION : "It is a process of retrieving the data by selecting only the columns is known as Projection " .

➢ In projection all the records / values present in a particular column are by default selected .

3. SELECTION : "It is a process of retrieving the data by selecting both the columns and rows is known as Selection " .

4. JOIN :"It is a process of retrieving the data from Multiple tables simultaneously is known as Join " .

 PROJECTION

➢ "It is a process of retrieving the data by selecting only the columns is known as Projection " .

➢ In projection all the records / values present in a particular column are by default selected .

SYNTAX :

SELECT * / [DISTINCT] Column_Name / Expression [ALIAS] FROM Table_Name ;

ORDER OF EXECUTION

1. FROM Clause

2. SELECT Clause

Example : Write a query to display names of all the students .

DAY 6 

EXPRESSION 

"A statement which gives result is known as Expression ".

 Expression is a combination Operand and Operator .

 Operand : These are the values that we pass .

 Operator : These are the Symbols which perform some Operation on the operand

Example: 5*10

1.    WAQTD name and salary given to the employees .

 SELECT ENAME , SAL FROM EMP ;

2.    WAQTD name and annual salary of the employees . SELECT ENAME , SAL * 12

3.    FROM EMP ;

 

4.     WAQTD all the details of the employee along with annual salary

 Select eid , ename , sal , sal*12 From emp ;

 Select emp.* , sal*12 From emp ;

 5.    WAQTD name and salary with a hike of 20% .

 Select ename , Sal + Sal*20/100 From emp ;

Formulae to calculate percentage :

6.    WAQTD name and salary of an employee with a deduction Of 10% .

 Select ename , sal - sal * 10 /100 From emp ;

ALIAS

"It is an alternate name given to a Column or an Expression In the result table " .

○ We can assign alias name with or without using 'As' keyword .

○ Alias names have to be a single string which is separated by An underscore or enclosed within double quotes 

 WAQTD annual salary for all the employees .

Select sal*12 From emp ;