1

By: Rohan ByanjankarSainik Awasiya Mahavidyalaya, Sallaghari, Bhaktapur

CONCEPT ON

STRUCTURED QUERY LANGUAGE (SQL)

2

Structured Query Language (SQL) is the special purposed programming language,

Main purpose of SQL to access data in Relational Database Management System,

RDBMS is the most revered DBMS, and basis for SQL, The data in RDBMS are recorded in relations or table, Relation is the pre-defined rows and column, where column contains

attributes, and tuples in rows, Oracle, SQL Server, MySQL are the examples…

Structured Query Language

3

Major Two Languages in RDBMS

Data Definition Language

Data Manipulation Language

4

• One of the fundamental requirements of SQL,• One is dumb in the SQL without knowledge of DDL,• Backbone of SQL,• Helps to develop overall design of database,• Helps to create, delete, and modify the database schema,• Not frequently used as database schema is not frequently

changed,

Data Definition Language (DDL)

5

•Create•Use •Drop •Alter

Basic DDL Commands

6

One of the fundamental commands,Use to establish many new independent database in DBMS,Use to create table within newly established database or existing

database,Syntax: - CREATE DATABASE SAMB- CREATE TABLE Students

Create Command

7

One of the fundamental commands,Helps to work on the newly established or created database,Syntax: - USE SAMB

Use Command

One of the DDL commands,Used to delete column of a table, entire table, and entire database,We must use drop command with intense care,Syntax: - DROP TABLE Student- DROP DATABASE SAMB

Drop Command

8

• Falls under the category of DDL command,• Used to change the structure of table without deleting or re-

creating the table,• Syntax:1. ALTER TABLE StudentADD Email_id VARCHAR(20)2. ALTER TABLE StudentDROP COLUMN Email_id

Alter Command

9

• One of the fundamental requirements of SQL,• DML helps to work on RDBMS,• Helps to change the content of RDBMS,• Helps to insert, select, update and delete the database

instances,• Frequently used as frequent modification is made in database,

Data Manipulation Language (DML)

10

• Insert• Select•Update•Delete

Basic DML Command

11

• Basic DML command,• Used to add new data in database,• The most frequently used,Syntax:• INERT INTO Student VALUES (0020, ‘Sujita Shrestha’, ‘BBA’,

16, ‘sujita98@gmail.com’)• INSERT INTO Student (sid, sname, grade) VALUES (0023,

‘Smiriti KC’, ‘BBA’)

Insert Command

12

• Enables to select data from database,• Syntax:• To select all• SELECT * FROM Student

• To select students with name staring from ‘S’• SELECT * FROM Student where sname=‘s%’

• To select students according to ID in descending order• SELECT * FROM StudentORDER BY sid desc

Select Command

13

• Used to update existing record in a table,• Syntax:• UPDATE table_name SET column1= Value1• For Example:• To update salary to 1.5 times of existing salary of an employee

with empid 19• UPDATE tblemployee SET salary = 1.5*salary

Update Command

WHERE empid= 19

14

• Enables us to remove the selected tuple or entire tuple without making alter to the table,• Syntax:• DELETE FROM table_name WHERE column1= ‘Value1’• For Example:• If Student with sid 0001 is needed to be removed from Student

table, then• DELETE FROM Student

Delete Command

WHERE sid= 0001

15

• SQL View is a logical table,• Created from the existing table,• Virtual table based on real table,• Constraints of Base table is applicable in View also.• Any modification in base table is reflected in View.• User can Use DML commands once the view is created.

SQL Views

16

CREATE VIEW View_name ASSELECT column1, column2, column3 FROMTable_1 WHERE column3= ‘Value1’For Example:To create view from table Product (Pid, Pname, Cost Price, Selling Price, Manu_date, Exp_date, Category) to Beverage DepartmentCREATE VIEW Beverage ASSELECT Pid, Pname, Selling Price, Manu_date, Exp_dateFROM Product WHERE Category= ‘Beverage’

Syntax

17

Pid Pname Cost Price Selling Price Manu_date Exp_date Category001 Parle-G 88 95 2014-01-05 2014-07-05 General002 Coca-Cola 130 149 2013-12-09 2014-06-09 Beverage003 Toberg 200 220 2013-11-14 2014-07-11 Beverage004 Sunflow Oil 300 350 2013-08-08 2014-08-08 General

Pid Pname Selling Price Manu_date Exp_date002 Coca-Cola 149 2013-12-09 2014-06-09003 Toberg 220 2013-11-14 2014-07-11

Product

Beverage

CREATE VIEW Beverage ASSELECT Pid, Pname, Selling Price, Manu_date, Exp_dateFROM Product WHERE Category= ‘Beverage’

Beverage Department

18

• A database index is a data structure that improves the speed of data retrieval operations on a database table,• used to quickly locate data without having to search every rowSyntax:CREATE INDEX index_name ONTable_name (column1)

Index

19

For Example: To create INDEX on table Library (ISBN, Bname, Price, Author)CREATE INDEX Book_index ONLibrary (ISBN)

Contd…ISBN Bname Price Author

000-124-456 The Old Man and The Sea Rs. 97 Ernest Hemingway

978-1-85326-067-4 Far from the Madding Crowd Rs. 200 Thomas Hardy

978-81-291-0818-0 One Night @ The Call Center Rs. 200 Chetan Bhagat

Library

20

• Those functions that perform a calculation on a set of values and return a single value.• MAX, MIN, AVG, COUNT are the examples…Syntax:1. SELECT Column1, MAX(Column2) AS MAX_Price FROM

Tbleproduct2. SELECT Column1, MIN(Column2) AS MIN_Price FROM

Tbleproduct3. SELECT Column1, AVG(Column2) AS AVG_Price FROM

Tbleproduct

Aggregate Function

21

SELECT Category, MAX(Selling_Price) AS MAX_SP FROM ProductGROUP BY Category

Contd…Pid Pname Cost_Price Selling_Price Manu_date Exp_date Category001 Parle-G 88 95 2014-01-05 2014-07-05 General002 Coca-Cola 130 149 2013-12-09 2014-06-09 Beverage003 Tuborg 200 220 2013-11-14 2014-07-11 Beverage004 Sunflow Oil 300 350 2013-08-08 2014-08-08 General

Product

Category MAX_SPBeverage 220General 350

22

• SQL join is the operation that enables us to get the combined Values of attributes of two tables,• The most common type of join is ‘Inner Join’.Syntax:SELECT column1, column2, column3, column5, column6 FROM Table1INNER JOIN Table2 ONTable1.Column1=Table2.column5Note:Data type of column1 and column5 must be identical

Joins

23

ISBN Bname Price Author

000-124-456 The Old Man and The Sea Rs. 97 Ernest Hemingway

978-1-85326-067-4 Far from the Madding Crowd Rs. 200 Thomas Hardy

978-81-291-0818-0 One Night @ The Call Center Rs. 200 Chetan Bhagat

ID Sname Grade ISBN1

0001 Sanjay Sharma BBA 978-81-291-0818-0

0002 Sushil Shrestha BSC 000-124-456

0030 Samikshaya Sharma BBA 978-1-85326-067-4

Library

Student

SELECT ISBN, Bname, ID, Sname, Grade, ISBN1 FROM LibraryINNER JOIN Student ONLibrary.ISBN= Student.ISBN1ISBN Bname ID Sname Grade

978-81-291-0818-0 One Night @ The Call Center 0001 Sanjay Sharma BBA

000-124-456 The Old Man and The Sea 0002 Sushil Shrestha BSC

978-1-85326-067-4 Far from the Madding Crowd 0030 Samikshaya Sharma BBA

24

Create Table Student with following attributes:

SQL Question:

ID Number Primary KeyName Text Not Null and

length<40Age Number >17 and <25Grade Text BBA or BSC

Not NullEmail Text Unique

Not NullContact Text Not Null

Length= 7 or 10

Address Text Not Null

CREATE TABLE Student (ID INT,Sname VARCHAR (50) NOT NULL,Age INT,Grade VARCHAR (8) NOT NULL,Email_id VARCHAR (30) UNIQUE NOT NULL,Contact VARCHAR (13) NOT NULL,Address VARCHAR (30) NOT NULL,CONSTRAINT pk_id PRIMARY KEY (ID),CONSTRAINT ch_values CHECK (LEN(Sname)<40),CONSTRAINT ch_values1 CHECK(Grade IN (‘BBA’,’BSC’)),CONSTRAINT ch_values2 CHECK (LEN(Contact)=7 OR LEN(Contact)=10));