Unit-3

1. Define SQL ? what are the features and advantages of SQL?

SQL:

 SQL stands for Structured Query Language.

 It is designed for managing data in a relational database management system
(RDBMS).
 It is pronounced as S-Q-L or sometime See-Qwell.
 SQL is a database language, it is used for database creation, deletion, fetching
rows and modifying rows etc.
 SQL is based on relational algebra and tuple relational calculus.

Features or characteristics of SQL:

1. SQL is easy to learn.

2. SQL is used to access data from relational database management systems.
3. SQL can execute queries against the database.
4. SQL is used to describe the data.
5. SQL is used to define the data in the database and manipulate it when needed.
6. SQL is used to create and drop the database and table.

Advantages of SQL

1. High speed

Using the SQL queries, the user can quickly and efficiently retrieve a large amount of
records from a database.

2. No coding needed

In the standard SQL, it is very easy to manage the database system. It no need to write
the code.

3.Well defined standards

Long established are used by the SQL databases that are being used by ISO and ANSI.

S. Rajeshwari ,Lecturer in Computers Page 1

 3. Portability

SQL can be used in laptop, PCs, server and even some mobile phones.

4. Interactive language

SQL is a domain language used to communicate with the database.

5. Multiple data view

Using the SQL language, the users can make different views of the database structure.

2 Q: what are the data types available in sql ?

SQL Data Types

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column
when creating a table.

Datatype description

1. char(size) it is used to fixed length of string. maximum size is

0 to 255

2.varchar2(size) it is used to fixed length of string(letters, digits, special

Symbols). Maximum length is 0 to 65535

3.number(size) it is used to specify integer values

4. number(n,m) it is used to specify floating values

5. date It is used to store the year, month, and days value.

6.time It is used to store the hour, minute, and second

values.

S. Rajeshwari ,Lecturer in Computers Page 2

3Q: what are the SQL commands ? (or) what are the DDL,DML,DCL commands ?

SQL command

o SQL commands are instructions. It is used to communicate with the database.

It is also used to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.

Types of SQL Command:

1. Data definition language (DDL)

 DDL means data definition language . it is used to creating a table, deleting a
table, altering a table, etc.

Here are some commands that come under DDL:

a) CREATE
b) ALTER
c) DROP
d) TRUNCATE

S. Rajeshwari ,Lecturer in Computers Page 3

 a. CREATE It is used to create a new table in the database.

Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB

DATE);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax DROP TABLE table name; ;

Example DROP TABLE EMPLOYEE;

c. ALTER:
It is used to alter the structure of the database. It is used for two purposes :
1) To add new column
2) Modify the column

Syntax: 1. To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

2. To modify existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

EXAMPLE

1. ALTER TABLE EMPLOYEE ADD(ADDRESS VARCHAR2(20));

2. ALTER TABLE Employee MODIFY (NAME VARCHAR2(20));

d. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.

Syntax:TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE EMPLOYEE;

S. Rajeshwari ,Lecturer in Computers Page 4

2. Data Manipulation Language
 DML commands are used to modify the database. It is responsible for all form of
changes in the database.
 The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.

Here are some commands that come under DML:

a) INSERT
b) UPDATE
c) DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the
row of a table.

Syntax: INSERT INTO TABLE_NAME

VALUES (value1, value2, value3, .... valueN);

For example:

INSERT INTO TABLE_NAME

VALUES (&value1, &value2, &value3, .... &valueN);
Note: if it is charcter type it must be enclosed with in the single quote . ex: ‘&name’

b. UPDATE: This command is used to update or modify the value of a column in the
table.

Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [W
HERE CONDITION]

For example:

UPDATE employee
SET Name = 'raju'
WHERE eno = 3;

c. DELETE: It is used to remove one or more row from a table.

S. Rajeshwari ,Lecturer in Computers Page 5

 Syntax: DELETE FROM table_name [WHERE condition];

For example: DELETE FROM employee WHERE eno=101;

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

 Grant
 Revoke

a. Grant: It is used to give user access privileges to a database.

Example

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

b. Revoke: It is used to take back permissions from the user.

Example

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and
UPDATE only.

These operations are automatically committed in the database that's why they cannot
be used while creating tables or dropping them.

Here are some commands that come under TCL:

a) COMMIT
b) ROLLBACK
c) SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

S. Rajeshwari ,Lecturer in Computers Page 6

 Syntax: COMMIT;

Example:

DELETE FROM employee

WHERE eno = 25;
COMMIT;

b. Rollback: Rollback command is used to undo transactions that have not already
been saved to the database.

Syntax: ROLLBACK;

Example:

DELETE FROM CUSTOMERS

WHERE AGE = 25;
ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling
back the entire transaction.

Syntax: SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command: SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is

used to select the attribute based on the condition described by WHERE clause.

Syntax: SELECT expressions FROM TABLES WHERE conditions;

For example:

SELECT name FROM employee WHERE age > 20;

S. Rajeshwari ,Lecturer in Computers Page 7

 Grouping results (or) SQL Clauses

4Q) explain about sql clauses or grouping results? (or) explain about the
following :

a)group by b) having clause c)order by clause

The following are the various SQL clauses:

1. GROUP BY
 SQL GROUP BY statement is used to arrange identical data into groups. The
GROUP BY statement is used with the SQL SELECT statement.
 The GROUP BY statement follows the WHERE clause in a SELECT statement
and precedes the ORDER BY clause.
 The GROUP BY statement is used with aggregation function.

Syntax

SELECT column FROM table_name

WHERE conditions
GROUP BY column
ORDER BY column

Sample table:

PRODUCT_MAST

S. Rajeshwari ,Lecturer in Computers Page 8

 PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Example:

SELECT COMPANY, COUNT(*)

FROM PRODUCT_MAST
GROUP BY COMPANY;

Output:

Com1 2
Com2 2
Com3 1

2. HAVING
 HAVING clause is used to specify a search condition for a group or an
aggregate.
 Having is used in a GROUP BY clause. If you are not using GROUP BY clause
then you can use HAVING function like a WHERE clause.

Syntax:

SELECT column1, column2

FROM table_name
WHERE conditions
GROUP BY column1, column2
HAVING conditions
ORDER BY column1, column2;

S. Rajeshwari ,Lecturer in Computers Page 9

 Example: SELECT COMPANY, COUNT(*)

FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>=2;

Output: Com1 2

Com2 2

3. ORDER BY
 The ORDER BY clause sorts the result-set in ascending or descending order.
 It sorts the records in ascending order by default. DESC keyword is used to sort
the records in descending order.

Syntax:

SELECT column1, column2 FROM table_name

WHERE condition
ORDER BY column1, column2... ASC|DESC;

Where

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

Example: Sorting Results in Ascending Order

Table:CUSTOMER

CUSTOMER_ID NAME ADDRESS

12 Kathrin US

23 David Bangkok

34 Alina Dubai

S. Rajeshwari ,Lecturer in Computers Page 10

SELECT * FROM CUSTOMER ORDER BY NAME;

Output:

CUSTOMER_ID NAME ADDRESS

34 Alina Dubai

23 David Bangkok

56 Harry US

Example: Sorting Results in Descending Order

Using the above CUSTOMER table

SELECT * FROM CUSTOMER

ORDER BY NAME DESC;

5Q) Define View ? how to create and delete view?

Views in SQL

 Views in SQL are considered as a virtual table. A view also contains rows and
columns.
 To create the view, we can select the fields from one or more tables present in
the database.
 A view can either have specific rows based on certain condition or all the rows of
a table.
 View name can be used as sub title for the table name in sql.

Sample table: Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram

S. Rajeshwari ,Lecturer in Computers Page 11

 Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19

2 Kathrin 86 21

3 David 74 18

4 Alina 90 20

5 John 96 18

1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view
from a single table or multiple tables.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE condition;

Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.

Query:

CREATE VIEW DetailsView AS

SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.

SELECT * FROM DetailsView;

OUTPUT

S. Rajeshwari ,Lecturer in Computers Page 12

 NAME ADDRESS

Stephan Delhi

Kathrin Noida

David Ghaziabad

3. Deleting View

A view can be deleted using the Drop View statement.

Syntax DROP VIEW view_name;

Example:

If we want to delete the View MarksView, we can do this as:

DROP VIEW MarksView;

6Q) Define index ? how to create and delete indexes in SQL ?

SQL Index

 Indexes are special lookup tables. It is used to retrieve data from the database
very fast.
 An index in a database is just like an index in the back of a book.
 For example: When you reference all pages in a book that discusses a certain
topic, you first have to refer to the index, which alphabetically lists all the topics
and then referred to one or more specific page numbers.

1. Create Index statement

It is used to create an index on a table. It allows duplicate value.

Syntax: CREATE INDEX index_name

ON table_name (column1, column2, ...);

Example: CREATE INDEX idx_name

S. Rajeshwari ,Lecturer in Computers Page 13

 ON Persons (LastName, FirstName);

2. Drop Index Statement

It is used to delete an index in a table.

Syntax DROP INDEX index_name;

Example DROP INDEX websites_idx;

Multi table queries :

7Q) explain about multi table queries?

(or) Explain about SQL joins ?

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN
means "to combine two or more tables".

In SQL, JOIN clause is used to combine the records from two or more tables in a
database.

Types of SQL JOIN

1. INNER JOINV 2. LEFT JOIN 3. RIGHT JOIN 4. FULL JOIN

Sample Table : EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

S. Rajeshwari ,Lecturer in Computers Page 14

 PROJECT

PROJECT_NO EMP_ID DEPARTMENT

101 1 Testing

102 2 Development

103 3 Designing

104 4 Development

1. INNER JOIN

In SQL, INNER JOIN selects records that have matching values in both tables as
long as the condition is satisfied. It returns the combination of all rows from both the
tables where the condition satisfies.

Syntax

SELECT table1.column1, table1.column2, table2.column1,.... FROM table1

INNER JOIN table2
ON table1.matching_column = table2.matching_column;

Query

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

FROM EMPLOYEE
INNER JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

OUTPUT

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

S. Rajeshwari ,Lecturer in Computers Page 15

 2. LEFT JOIN

The SQL left join returns all the values from left table and the matching values from
the right table. If there is no matching join value, it will return NULL.

Syntax

SELECT table1.column1, table1.column2, table2.column1,....

FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Query

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

FROM EMPLOYEE
LEFT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

Russell NULL

Marry NULL

3. RIGHT JOIN

In SQL, RIGHT JOIN returns all the values from the values from the rows of right
table and the matched values from the left table. If there is no matching in both
tables, it will return NULL.

Syntax

SELECT table1.column1, table1.column2, table2.column1,....

FROM table1
S. Rajeshwari ,Lecturer in Computers Page 16
 RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Query

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

FROM EMPLOYEE
RIGHT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

OUTPUT:

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

4. FULL JOIN

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join
tables have all the records from both tables. It puts NULL on the place of matches
not found.

Syntax

SELECT table1.column1, table1.column2, table2.column1,....

FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Query

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

FROM EMPLOYEE
FULL JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

S. Rajeshwari ,Lecturer in Computers Page 17

 OUTPUT:

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

Russell NULL

Marry NULL

S. Rajeshwari ,Lecturer in Computers Page 18