Chapter-07 - SQL

CSE 21031
SQL
For your Reference
SQL Components: DDL, DCL, & DML
• SQL is a very large and powerful language, but every type of SQL
statement falls within one of three main categories (or sub-
languages):
• Data Definition Language (DDL) for creating a DB
e.g. CREATE, DROP, ALTER
• Data Control Language (DCL) for administering a DB
e.g. GRANT, DENY, USE
• Data Manipulation Language (DML) to access a DB
e.g. SELECT, INSERT, UPDATE, DELETE
Relational Tables Can Answer
Many Queries
Student Enrolment Course
• How many courses are there & what are their names?
• Which students are enrolled for Java?
• How many students take 3 or more courses?
SQL - Structured Query Language
• SQL was developed at IBM around 1975...
• Structured programming?
No! - Structured English (from ‘SEQUEL’)
(for Structured English QUEry Language)
• SQL is a declarative language - says what not how
• SQL is an abstract & portable interface to RDMBs
• Warning: different vendors have dialects & extensions

Structured Query Language (contd.)
SQL is a comprehensive database language:
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Facilities for security & authorization

• Facilities for transaction processing
• Facilities for embedding SQL in general purpose languages (Embedded SQL)

SQL Syntax
• SQL uses English keywords & user-defined names

CREATE TABLE Staff (
StaffNo INTEGER,
Salary FLOAT,
Lname CHAR(20)
);
INSERT INTO Staff VALUES (32, 25000.0, 'Smith');
• By convention, keywords are upper-case

• Text data is enclosed using single quotes (‘ ' ‘)
• Round brackets (‘(‘) are used to group related items
• Commas (‘,’) separate items in a list
• Statements are terminated with a semicolon (‘;’)
SQL Terminology
• SQL does not use formal relational terminology
Formal Informal (SQL)

Relation Table
Tuple Row
Attribute Column
Cardinality No. of rows
Degree No. of columns
Relationships Foreign keys
Constraints Assertions
• Creating tables…
• Tables can be created using CREATE TABLE statement

• There are different data types available in SQL2
• Integer (INT, INTEGER & SMALLINT)
• Real numbers (FLOAT, REAL, DOUBLE)
• Formatted numbers DECIMAL(i,j) or DEC(i,j) or NUMERIC(i,j)
• Character strings
• Fixed length: CHAR(n) or CHARACTER(n)
• Variable length: VARCHAR(n)
• Date and Time: DATE and TIME data types are supported in SQL2
Example…
CREATE TABLE Student (
name CHAR(20),
address CHAR(25),
age INTEGER,
gpa REAL
);
• Primary Key constraint
• PRIMARY KEY

stdid CHAR (10) PRIMARY KEY,
name CHAR(20),
address CHAR(25),
age INTEGER,
gpa REAL
);
• Alternative:
stdid CHAR (10),
name CHAR(20),
address CHAR(25),
age INTEGER,
gpa REAL,
PRIMARY KEY (stdid)
);
• Other candidate keys…

• UNIQUE

Stdid CHAR (10) PRIMARY KEY,
Name CHAR(20),
AddressCHAR(25),
Age INTEGER,
Gpa REAL,
NIC CHAR(10) UNIQUE);
• Referential Integrity Constraints
• FOREIGN KEY………REFERENCES
• Student (stdid, name ,address , age , gpa)
• Grade( subjectId, stdid , grade)
CREATE TABLE Grade

(
subjectId CHAR(4),
stdId CHAR(10),
grade CHAR(2),
PRIMARY KEY(subjectId, stdid),
FOREIGN KEY(stdid) REFERENCES Student
)
• Alternative
• FOREIGN KEY
CREATE TABLE Grade (

subjectId CHAR(4),
stdid CHAR(10) REFERENCES Student(stdid),
grade CHAR(2),
PRIMARY KEY(subjectId,stdid)
);
• Alternative:
CREATE TABLE Grade (
subjectId CHAR(4),
stdid CHAR(10),
grade CHAR(2),
PRIMARY KEY(subjectId,stdid),
CONSTRAINT fk_Grade FOREIGN KEY(stdid)
REFERENCES Student(stdid)
);
• Specifying NOT NULL constraints…

• NOT NULL

Name CHAR(20) NOT NULL,
AddressCHAR(25),
Age INTEGER,
Gpa REAL
);
• The use of a constraint name allows identification of a constraint which

can be dropped later (using the ALTER TABLE command)
• Dropping tables
DROP TABLE Employee [RESTRICT|CASCADE]
• RESTRICT drops if no other constraints (such as foreign keys or

views exist)
• CASCADE drops all constraints & views that reference it
* SQL Server 2000 has only RESTRICT option which is the default
• Altering tables…
• ALTER TABLE can be used to add or drop a column, change a column
definition, and adding or dropping table constraints
• For example…
ALTER TABLE Employee ADD Job VARCHAR(12)
-- The new column has null values. Hence, NOT NULL cannot be used here
• Dropping a column…
ALTER TABLE Employee DROP COLUMN Job
• Changing column definition…
ALTER TABLE Employee ALTER COLUMN job vachar(50)

• Dropping a constraint
ALTER TABLE Employee DROP fk_EmpDept

• We can modify data using the following three commands: INSERT, DELETE and
UPDATE
• INSERT statement:
• Inserting a single row
INSERT INTO Dept VALUES (1, 'Sales', 'BoC Merchant Tower')
*The order of values must be the same as in the CREATE TABLE statement
•Inserting to user-specified columns
INSERT INTO Employee (NIC, name, works_in)

VALUES ('781111111V', 'Ajith Perera', 1)
* Only the columns are specified are filled with values. Unspecified
columns are filled with NULL if there is no default values.
• Inserting multiple rows…
INSERT INTO Employees

<Select statement>*
• Deleting tuples/record from tables…

• Deleting all records
DELETE FROM Dept

• Deleting only specified records
DELETE FROM <table>
WHERE <selection-condition>
DELETE FROM Dept WHERE dno = 2

• Completely removing a table is a DDL operation
DROP TABLE Staff

• Updating tuples in a table
UPDATE <table>
SET <column> = <expression>
WHERE <selection condition>
• Updating all record
UPDATE Employee SET works_in = 0
• Updating selected tuples

UPDATE Employee
SET works_in = 2
WHERE NIC = ‘781111111V’
Exercises…
• Consider the following schema:
Student(StudentNo:Integer; name:varchar(50), major:char(4);
GPA:float)
Write SQL statements to perform the following

1. Create the above table
2. Insert the following information:
StudentNo Name Major GPA

1 josh EE 3.5
2 mery CSE 3.4
Exercises…
3. Update josh’s GPA to 3.7.

4. Delete student table
5. Add a column address (i.e. address: varchar(50) ) to the
Student table.
6. Change the data type of address column into varchar(100).
7. Add a checking rule to GPA column, GPA values should
between 0 and 4.
8. Remove the check constraint added to the GPA column.
9. Create table Major(majorId , description).
10. Change student table to reflect Primary Key.

Chapter-07 - SQL

Uploaded by

Copyright:

Available Formats

Chapter-07 - SQL

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter-07 - SQL

Uploaded by

Copyright:

Available Formats

CSE 21031

• SQL was developed at IBM around 1975...

• SQL is a declarative language - says what not how

• SQL is an abstract & portable interface to RDMBs

• Warning: different vendors have dialects & extensions

SQL is a comprehensive database language:

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Facilities for security & authorization

• Facilities for embedding SQL in general purpose languages (Embedded SQL)

• SQL uses English keywords & user-defined names

• By convention, keywords are upper-case

• SQL does not use formal relational terminology

Formal Informal (SQL)

• Tables can be created using CREATE TABLE statement

• Integer (INT, INTEGER & SMALLINT)

• Real numbers (FLOAT, REAL, DOUBLE)

• Formatted numbers DECIMAL(i,j) or DEC(i,j) or NUMERIC(i,j)

CREATE TABLE Student (

• Other candidate keys…

CREATE TABLE Student (

CREATE TABLE Grade

CREATE TABLE Grade (

• Specifying NOT NULL constraints…

CREATE TABLE Student (

• The use of a constraint name allows identification of a constraint which

DROP TABLE Employee [RESTRICT|CASCADE]

• RESTRICT drops if no other constraints (such as foreign keys or

ALTER TABLE Employee ADD Job VARCHAR(12)

ALTER TABLE Employee DROP COLUMN Job

• Changing column definition…

ALTER TABLE Employee ALTER COLUMN job vachar(50)

ALTER TABLE Employee DROP fk_EmpDept

INSERT INTO Dept VALUES (1, 'Sales', 'BoC Merchant Tower')

•Inserting to user-specified columns

INSERT INTO Employee (NIC, name, works_in)

• Inserting multiple rows…

INSERT INTO Employees

• Deleting tuples/record from tables…

DELETE FROM Dept

DELETE FROM Dept WHERE dno = 2

DROP TABLE Staff

• Updating tuples in a table

• Updating all record

UPDATE Employee SET works_in = 0

• Updating selected tuples

Write SQL statements to perform the following

StudentNo Name Major GPA

3. Update josh’s GPA to 3.7.

You might also like