SQL

Er. Nawaraj Bhandari
Topic 7
SQL 1

Objectives of SQL
 Create the database and relation structures
 Perform basic tasks such as inserts, updates and deletes
 Simple and complex queries
Structured Query Language

Data Definition Language: DDL
 For defining database structures and controlling access to data
 CREATE TABLE, CREATE INDEX , CREATE SEQUENCE , GRANT ACCESS
etc.

Data Manipulation Language: DML
 For retrieving and updating data
 SELECT, INSERT, UPDATE, DELETE

History of SQL - 1
 Developed from IBM’s SYSTEM R
 The database standard language by 1970s...
 First standard published in 1987 by ISO

History of SQL - 2
 Addition to standard published 1989
 SQL 1992 – Major revision ‘SQL-92’
 SQL: 1999 - with object-relational features
 SQL: 2003 - introduced concept of ‘core SQL’
 SQL: 2006 – user of SQL with XML
 SQL: 2008 minor revisions.

Data Manipulation Language: DML
 For retrieving and updating data
 SELECT – retrieving
 INSERT, UPDATE, DELETE – updating
Difference between INSERT and UPDATE?

Literals
 Insert into Student (Stu_id, First_name, Last_name)
 Values (1,’Satpal’,’Singh’);
- Non-numeric in single quotes
- Numeric NOT in quotes

Select branchID, Count(staff_id)
From workers
Where branchType = ‘Main’
Group by branchID
Having Count (staff_id) > 1
Order by branchID

Select branchID, Count(staff_id)
From workers
Where branchType = ‘Main’
Group by branchID
Having Count (staff_id) > 1
Order by branchID
Which columns will appear?
Which table?
Condition
Groups by some column
Value
Restricts what will
Be grouped
Specifies the order of the result

Database Update
 Insert
 Update
 Delete

Insert
 Insert into students (Student_id, first_name, last_name)
 Values (1,’Satpal’,’Singh’);
 Insert into students
 Values (1,’Satpal’,’Singh’,’Overseas’,’Undergrad’);

Update
 Update Students
 Set Student_type = ‘Undergrad’;
 Update Students
 Set student_type = ‘Undergrad’
 Where student_id = 1;

Delete
 Delete from Students;
 Delete from Students
 Where student_id = 1;

Activity
 Departments
 Department_Id Integer
 Department_Name varchar 30
 Location varchar 30

 Departments
 Department_Id Integer
 Department_Name varchar 30
 Location varchar 30
• Insert into Departments
• Values (8,’Complaints’,’Glasgow’):

Datatypes
 What is a datatype?
 What is a domain?

String Datatypes
 Character or Char
 Varying Character of Varchar
 Bit (N)
 Bit varying

Char or Varchar
 ‘Gary__’ a 6 long Char
 ‘Gary’ a 6 long varchar

Numeric Datatypes
 Numeric or Decimal, e.g. 8.23 with point set
 Integer, e.g. 8
 Float, e.g. 8.23 but could also be changed so that point moves when
needed

Datetime Types
 Date
 Time
 Timestamp
 Interval

Advantages of SQL
 Universal
 Easy to use
 Fits (more or less) with relational model

Disadvantages of SQL
 Does not support all features of relational model
 No one standard
 Has had to be extended
 Much redundancy – possible to do the same thing many ways

Learning Outcomes – Have We Met Them?
 By the end of this topic, students will be able to:
 Explain the purpose of SQL
 Outline the basic concepts of SQL
 Understand that there are different ‘flavours’ of SQL

References
 Benyon-Davis, P. (2003). Database Systems, 3rd edition. Palgrave Macmillan.
Chapters 11, 12 & 13.
 Connolly, T. & Begg, C. (2004). Database Systems: A Practical Approach to
Design, Implementation, and Management, 4th Edition. Addison Wesley.
Chapters 5, 6 & 7.
 Dietrich, S. W. (2001). Understanding Relational Database Query Languages, 1st
edition. Prentice Hall. Chapter 5.

SQL

In this document

More Related Content

What's hot

Viewers also liked

Similar to SQL

More from Bhandari Nawaraj

Recently uploaded

SQL