UNIT- II

• Relational Model: Introduction to relational model,

concepts of domain, attribute, tuple, relation,
importance of null values, constraints (Key constraints,
integrity constraints) and their importance

• BASIC SQL: Simple Database schema, data types, table

definitions (create, alter), different DML operations
(insert, delete, update), basic SQL querying (select and
project) using where clause, arithmetic & logical
operations, SQL functions (Date and Time, Numeric,
String conversion).
Structured Query Language –
The Basics

BASIC SQL
 SQL stands for Structured Query Language
SQL is a standard language for accessing and manipulating databases.

SQL became a standard of the

• American National Standards Institute (ANSI) in 1986

• International Organization for Standardization (ISO) in 1987

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
•
 SQL Environment
• Catalog
– A set of schemas that constitute the description of a database

• Schema
– The structure that contains descriptions of objects created by a user (base tables, views, constraints)

• Data Definition Language (DDL)

– Commands that define a database, including creating, altering, and dropping tables and establishing
constraints

• Data Manipulation Language (DML)

– Commands that maintain and query a database

• Data Control Language (DCL)

– Commands that control a database, including administering privileges and committing data
 Overview of SQL
– Data Definition Language

• Creating tables

– Data Manipulation Language

• Inserting/Updating/Deleting data
• Retrieving data
– Single table queries
– Where
– Joins
– Grouping
 SQL
• SQL is a data manipulation language.

• SQL is not a programming language.

• SQL commands are interpreted by the DBMS

engine.

• SQL commands can be used interactively as a query

language within the DBMS.

• SQL commands can be embedded within

programming languages.
 3 Types of SQL Commands
• Data Definition Language (DDL):
– Commands that define a database - Create, Alter,
Drop

• Data Manipulation Language (DML)

– Commands that maintain and query a database.

• Data Control Language (DCL)

– Commands that control a database, including
administering privileges and committing data.
 Data Manipulation Language (DML)

Four basic commands:

• INSERT
• UPDATE
• DELETE
• SELECT
DDL, DML, DCL, and the database development process
 Table name Attribute names
Tables in SQL
Product

PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks

Powergizmo 29.99 Gadgets GizmoWorks

SingleTouch 149.99 Photography Canon

MultiTouch 203.99 Household Hitachi

Tuples or rows
 Tables Explained

• The schema of a table is the table name and

its attributes:
Product(PName, Price, Category, Manfacturer)

• A key is an attribute whose values are

unique;

Product(PName, Price, Category, Manfacturer)

 Steps in Table Creation
1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (Primary key)
4. Identify primary key–foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table and associated indexes
 SQL Query
Basic form:

SELECT <attributes>
FROM <one or more relations>
WHERE <conditions>
 Notation
Input Schema

Product(PName, Price, Category, Manfacturer)

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100

Answer(PName, Price, Manfacturer)

Output Schema
• Case insensitive:
– Same: SELECT Select select
– Same: Product product
– Different: ‘Seattle’ ‘seattle’

• Constants:
– ‘abc’ - yes
– “abc” - no
 Removing Tables

• DROP TABLE statement allows you to

remove tables from your schema:

–DROP TABLE CUSTOMER_T

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");
select * from Info;
 Inserting Data into a Table
INSERT INTO tablename (column-list) VALUES
(value-list)

PUTS ONE ROW INTO A TABLE

INSERT INTO COURSE

(COURSE_CODE, COURSE_NAME, CREDIT_HOURS)
VALUES (‘MIS499’,’ADVANCED ORACLE’,4);
 More on Inserting Data
INSERT INTO COURSE
VALUES (‘MIS499’,’ADVANCED ORACLE’,4);
COLUMN LIST IS OPTIONAL IF YOU PLAN TO
INSERT A VALUE IN EVERY COLUMN AND IN
THE SAME ORDER AS IN THE TABLE

INSERT INTO COURSE

(COURSE_NAME, COURSE_CODE, CREDIT_HOURS)
VALUES (’ADVANCED ORACLE’,‘MIS499’,4);

COLUMN LIST IS NEEDED

NOTE - TABLE STILL HAS THE
TO CHANGE THEORDER
ORIGINAL COLUMN ORDER
- MUST MATCH VALUE LIST
 Inserting Null Data
INSERT INTO COURSE
(COURSE_CODE, CREDIT_HOURS)
VALUES (‘MIS499’,4); COLUMN LIST IS NEEDED IF
YOU PLAN TO LEAVE OUT A
VALUE IN THE VALUE LIST

INSERT INTO COURSE COLUMN LIST CAN BE OMITTED

VALUES (‘MIS499’,’’,4); IF YOU PUT IN A BLANK VALUE

THE NULL KEYWORD CAN

INSERT INTO COURSE BE USED TO CREATE A BLANK
COLUMN
VALUES (‘MIS499’,NULL,4);

ALL OF THESE ASSUME THAT THE DATABASE ALLOWS THE COLUMN TO

BE NULL. YOU CANNOT LEAVE PRIMARY KEYS AND FOREIGN KEYS BLANK
 Insert Statement
• Adds one or more rows to a table
• Inserting into a table

• Inserting from another table

 Update Statement

• Modifies data in existing rows

37
 Updating Data
UPDATE COURSE SET HOURS=5;
CHANGES EVERY ROW

UPDATE COURSE SET HOURS=5

WHERE COURSE_CODE=‘MIS220’
CHANGES ONE ROW

UPDATE COURSE SET HOURS=3

WHERE COURSE_CODE LIKE ‘MIS%’
CHANGES A GROUP OF ROWS
 Updating and Integrity Constraints
You Can Change The Value of a Foreign Key as
long as The New Value also complies with
Referential Integrity Constraints.

Primary Key values can be updated as long as

there are No Rows in other Tables with Foreign
Keys with the same value

DOES NOT MATTER IF CONSTRAINT IS

RESTRICTED OR CASCADED
 Integrity Error
SQL> UPDATE COURSE
SET COURSE_CODE='MIS221‘
WHERE COURSE_CODE='MIS220';
UPDATE COURSE

ERROR :
integrity constraint violated - child record found
 Delete Statement
Removes rows from a table
Delete certain rows
DELETE FROM CUSTOMER_T WHERE
CUSTOMERSTATE = ‘HI’;
Delete all rows
DELETE FROM CUSTOMER_T;

43
 Deleting Data
Be careful!! This deletes ALL of
the rows in your table. If you
DELETE COURSE; use this command in error, you
can use ROLLBACK to undo
Deletes All Rows the changes.

DELETE COURSE WHERE COURSE_CODE =

‘MIS220’;
Deletes Specific Rows

DELETE COURSE WHERE HOURS=4;

Deletes A Group Of Rows

DELETE COURSE WHERE HOURS<4;

 Deleting and Integrity Constraints
SQL> DELETE COURSE
WHERE COURSE_CODE='MIS220';

ERROR at line 1:
integrity constraint violated - child record
found
 SELECT Statement
• Used for queries on single or multiple tables
• Clauses of the SELECT statement:
SELECT
- List the columns (and expressions) to be returned from the query
FROM
- Indicate the table(s) or view(s) from which data will be obtained
WHERE
- Indicate the conditions under which a row will be included in the result
GROUP BY
- Indicate categorization of results
HAVING
- Indicate the conditions under which a category (group) will be included
ORDER BY
- Sorts the result according to specified criteria
47
SQL statement
processing
 SELECT Example
• Find products with standard price less than $275

Table: Comparison Operators in SQL

49
 SQL - Other Features
• DISTINCT
• Arithmetic operators: +, -, *, /
• Comparison operators: =, >, >=, <, <=, <>
• Concatenation operator: ||
• Substring comparisons: %, _
• BETWEEN
• AND, OR, NOT
 The LIKE operator

SELECT *
FROM Products
WHERE PName LIKE ‘%gizmo%’

• s LIKE p: pattern matching on strings

• p may contain two special symbols:
– % = any sequence of characters
– _ = any single character
 Eliminating Duplicates
Category
SELECT DISTINCT category Gadgets
FROM Product
Photography
Household

Compare to:

Category
Gadgets
SELECT category
Gadgets
FROM Product
Photography
Household
 Ordering the Results

SELECT pname, price, manufacturer

FROM Product
WHERE category=‘gizmo’ AND price > 50
ORDER BY price, pname

Ties are broken by the second attribute on the ORDER BY list, etc.

Ordering is ascending, unless you specify the DESC keyword.

 PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT DISTINCT category

FROM Product
ORDER BY category

SELECT Category
FROM Product
ORDER BY PName

SELECT DISTINCT category

FROM Product
ORDER BY PName
 Keys and Foreign Keys
Company

CName StockPrice Country

Key GizmoWorks 25 USA

Canon 65 Japan

Hitachi 15 Japan

Product

PName Price Category CName

Foreign
Gizmo $19.99 Gadgets GizmoWorks key
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
 WHERE Conditions
SELECT * FROM COURSE
WHERE COURSE_CODE LIKE ‘MIS%’;
USE % TO SUBSTITUTE FOR
ANY STRING

SELECT * FROM COURSE

WHERE CREDIT HOURS BETWEEN 3 AND 5;
3 AND 5 ARE INCLUDED

SELECT * FROM CUSTOMER

WHERE BALANCE < CREDIT_LIMIT;
YOU CAN COMPARE TWO
COLUMNS
 More WHERE Conditions
SELECT * FROM CUSTOMER
WHERE STATE IN (‘OH’,’WV’,’KY’);
LIST OF SPECIFIC VALUES TO
LOOK FOR

SELECT * FROM CUSTOMER

WHERE (CREDIT_LIMIT - BALANCE) <1000;

CAN MANIPULATE NUMBER

VALUES MATHMATICALLY
 AND/OR/NOT Conditions
SELECT * FROM CUSTOMER
WHERE BALANCE >=500
TWO COMPARISONS
AND BALANCE<=1000;
ON THE SAME COLUMN

SELECT * FROM CUSTOMER TWO COMPARISONS

WHERE STATE = ‘OH’ ON THE DIFFERENT
OR CREDIT_LIMIT>1000; COLUMNS

SELECT * FROM CUSTOMER SAME AS

WHERE NOT (STATE=‘OH’); STATE<>‘OH’
 More on AND/OR/NOT
SELECT * FROM CUSTOMER
Use parentheses to
WHERE STATE = ‘OH’ make complex logic
OR (CREDIT_LIMIT=1000
more understandable.
AND BALANCE <500);
CUST STATE LIMIT BAL
A OH 1000 600
B WV 1000 200
C OH 500 300 Who will be selected??
D OH 1000 200
E KY 1300 800
F KY 1000 700
G MA 200 100
H NB 1000 100
 SQL for Retrieving Data from Two or
More Tables
SQL provides two ways to retrieve data from related
tables:

• Join - When two or more tables are joined by a

common field.

• Subqueries - When one Select command is nested

within another command.
SQL Keywords
SQL Keywords
SQL Keywords
SQL Keywords
 Questions
1. Give syntax and apply the DDL and DML commands for
defining and constructing two tables of your choice with
appropriate data.

2. Illustrate different Integrity constraints in relational model

with appropriate examples

3. Explain Relational Query languages with proper Examples.

4. Give syntax and apply the SQL commands for defining two
example tables of your choice. Then insert data, update data
in the tables
 Questions
4. Explain Primary and Foreign Key constraints with examples.

5. Consider the following schema of a bank database.

Branches (B_name: string, B_city:string, Assets_Value:integer)
Accounts(Acc_No:string, B_name:string, Balance:real)
Loans(L_No:string, B_name:sring, Amount:real).

Create tables for the above schema with primary key, foreign
key and not null constraints wherever necessary.
 Asignment Question
7. Consider the following schema and answer the following queries in
SQL.
Suppliers( sid:integer, sname:string, address:string)
Parts(pid:integer, pname:string, colour:string)
Catalog( sid:integer, pid:integer, cost:real)

i)Find the ids and names of suppliers whose name begins with
character “A”.
ii)Find the ids of suppliers who supply some Red colour part.
iii)Find the ids of suppliers who supply either a Red colour part or a
Green colour part.
iv)Find the names of all parts supplied by supplier named John.
 Assignment Question
7. Consider the following schema and answer the following queries in
SQL.
Students(sid: string, sname:string, Date_of_Birth:date, GPA:real)

Courses(cid:string, cname:string, credits:integer, offered_by:string)

Enrolled(sid:string, cid:string)

i) For each course offered by CSE department, find the total number of
enrollments.

ii) Find the sum of credits of all courses taken by student “ S01”.

iii) Find the courses that have at least 10 enrollments.

 Assignment Question
• Based on the below schemas construct the corresponding SQL queries
•
Sailors (sid:string, sname:string, rating:integer, age:real)

Boats (bid:integer, bname:string, color:string)

Reserves (sid:integer, bid:integer, day:date)

• Find the colors of boats reserved by Lubber.

• Find the names of sailors who have reserved at least one boat.
• Find the names of sailors who have reserved both a red and a green boat.
• Find the names of sailors who have reserved all boats.
• Display the names of sailors whose name starts with “S”.
• Display all the sailor’s names alphabetical order.
• Display the sid and number of boats reserved by each sailor