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
