Study Notes on SQL

SQL

Structured Query language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970, for use in system R and is a defector standard, as well as an ISO and ANSI standard.

• To deal with the above database objects, we need a programming language and that programming languages is known as SQL.

Three subordinate languages of SQL are:

Data Definition Language (DDL)

It includes the commands as

• CREATE To create tables in the database.
• ALTER To modify the existing table structure:
• DROP To drop the table with table structure.
• Data Manipulation Language (DML) It is’ used to insert, delete, update data and perform queries on these tables. Some of the DML commands are given below.
• INSERT To insert data into the table.
• SELECT To retrieve data from the table.
• UPDATE To-update existing data in the table.
• DELETE To delete data from the table.

Data Control Language (DCL)

It’ is used to control user’s access to the database objects. Some of the DCL commands are:

• GRANT Used to grant select/insert/delete access.
• REVOKE Used to revoke the provided access

Transaction Control Language (TCL): It is used to manage changes affecting the data.

• COMMIT To save the work done, such as inserting or updating or deleting data to/from the table.
• ROLLBACK To restore database to the original state, since last commit.
• SQL Data Types SQL data types specify the type, size and format of data/information that can be stored in columns and variables.

Formulas for GATE Computer Science Engineering – Databases

Constraint Types with Description

Default Constraint: It is used to insert a default value into a column, if no other value is specified at the time of insertion.

Syntax

CREATE TABLE Employee

{

Emp_idint NOT NULL,

Last_Name varchar (250),

City varchar (50)OEFAULT *BANGALURU*

}

DDL Commands

1. CREATE TABLE < Tab1e_Name> { Co1umn_name 1< data_type >, Column_name 2 < d’lta_type > }
2. ALTER TABLE < Table_Name> ALTER Column < Column_Name> SET NOT NULL
3. RENAME < object_type >object_name > to
4. DROP TABLE

DML Commands

SELECT A₁, A₂, A₃……,A_n what to return

FROM R₁, R₂, R₃, ….., R_m relations or table

WHERE condition filter condition i.e., on what basis, we want to restrict the outcome/result.

If we want to write the above SQL script in the form of relational calculus, we use the following syntax

Comparison operators which we can use in filter condition are (=, >, <, > = , < =, < >,) ‘< >’ means not equal to.

INSERT Statement: Used to add row (s) to the tables in a database

INSERT INTO Employee (F_Name, L_Name) VALUES (‘Atal’, ‘Bihari’)

UPDATE Statement: It is used to modify/update or change existing data in single row, group of rows or all the rows in a table.

Example: //Updates some rows in a table. UPDATE Employee SET City = ‘LUCKNOW’ WHERE Emp_Id BETWEEN 9 AND 15; //Update city column for all the rows UPDATE Employee SET City=’LUCKNOW’;

DELETE Statement

This is used to delete rows from a table,

Example:

//Following query will delete all the rows from Employee table DELETE Employee Emp_Id=7; DELETE Employee

ORDER BY Clause: This clause is used to, sort the result of a query in a specific order (ascending or descending), by default sorting order is ascending.

SELECT Emp_Id, Emp_Name, City FROM Employee

WHERE City = ‘LUCKNOW’

ORDER BY Emp_Id DESC;

GROUP BY Clause: It is used to divide the result set into groups. Grouping can be done by a column name or by the results of computed columns when using numeric data types.

• The HAVING clause can be used to set conditions for the GROUPBY clause.
• HAVING clause is similar to the WHERE clause, but having puts conditions on groups.
• WHERE clause places conditions on rows.
• WHERE clause can’t include aggregate: function, while HAVING conditions can do so.

Example:

SELECT Emp_Id, AVG (Salary)

FROM Employee

GROUP BY Emp_Id

HAVING AVG (Salary) > 25000;

Aggregate Functions

Joins: Joins are needed to retrieve data from two tables’ related rows on the basis of some condition which satisfies both the tables. Mandatory condition to join is that atleast one set of column (s) should be taking values from same domain in each table.

Inner Join: Inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. These may be further divided into three parts.

1. Equi Join (satisfies equality condition)
2. Non-Equi Join (satisfies non-equality condition)
3. Self Join (one or more column assumes the same domain of values).

Outer Join: An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record-even if no other matching record exists.

Considers also the rows from table (s) even if they don’t satisfy the joining condition

(i) Right outer join (ii) Left outer join (iii) Full outer join

Left Outer Join: The result of a left outer join for table A and B always contains all records of the left table (A), even if the join condition does not find any matching record in the right table (B).

Result set of T1 and T2

Right Outer Join: A right outer closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the right table will appear in the joined table at least once. If no matching with left table exists, NULL will appear.

Result set of T1 and T2

Full Outer Join: A full outer join combines the effect of applying both left and right outer joins where records in the FULL OUTER JOIN table do not match, the result set will have NULL values for every column of the table that lacks a matching row for those records that do match, as single row will be produced in the result set.

Result set of T1 and T2 (Using tables of the previous example)

Cross Join (Cartesian product): Cross join returns the Cartesian product of rows form tables in the join. It will produce rows which combine each row from the first table with each row from the second table.

Select * FROM T1, T2

Number of rows in result set = (Number of rows in table 1 × Number of rows in table 2)

Result set of T1 and T2 (Using previous tables T1 and T2)

• Introduction to Databases
• ER-model
• Relational model
• Database Design
• File organization
• Transactions and Concurrency Control

Online Classroom Program

BYJU’S Exam Prep Test Series

Thanks