Database Management System II 1 Chapter 1 Introduction

Database Management System - II 1

Chapter# 1 Introduction to Structured Query Language(SQL) 2

What is SQL? SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System. All relational database management systems like My. SQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language. 3

Why SQL? Allows users to access data in relational database management systems. Allows users to describe the data. Allows users to define the data in database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views 4

SQL Commands: The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature: DDL - Data Definition Language: Command Description CREATE Creates a new table, a view of a table, or other object in database ALTER Modifies an existing database object, such as a table. DROP Deletes an entire table, a view of a table or other object in the database. 5

SQL Commands: DML- Data Manipulation Language: Command Description INSERT Creates a record UPDATE Modifies existing records DELETE Deletes records DQL- Data Query Language: Command Description SELECT Retrieves certain records from one or more tables 6

SQL RDBMS Concepts What is table? The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows. Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table: 7

SQL RDBMS Concepts What is field? Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a column in a table that is designed to maintain specific information about every record in the table. What is record or row? A record, also called a row of data, is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table: 8

SQL RDBMS Concepts What is column? A column is a vertical entity in a table that contains all information associated with a specific field in a table. For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would consist of the following: What is NULL value? A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. 9

SQL Constraint SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). CREATE TABLE table_name ( column_name 1 data_type(size) constraint_name, column_name 2 data_type(size) constraint_name, column_name 3 data_type(size) constraint_name, . . ) 10

SQL Constraint In SQL, we have the following constraints: NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value when specified none for this column 11

SQL NOT NULL Constraint The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. CREATE TABLE Persons. Not. Null ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255) ) 12

SQL UNIQUE Constraint The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. CREATE TABLE Persons ( P_Id int UNIQUE, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255) ) 13

SQL UNIQUE Constraint To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_Person. ID UNIQUE (P_Id, Last. Name) ) 14

SQL UNIQUE Constraint To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD UNIQUE (P_Id) To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax: ALTER TABLE Persons ADD CONSTRAINT uc_Person. ID UNIQUE (P_Id, Last. Name) To drop a UNIQUE constraint, use the following SQL: ALTER TABLE Persons DROP INDEX uc_Person. ID 15

SQL Primary Key Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key. CREATE TABLE Persons ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) 16

SQL Primary Key Constraint To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL CREATE TABLE Persons syntax: ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_Person. ID PRIMARY KEY (P_Id, Last. Name) ) Note: In the example above there is only ONE PRIMARY KEY (pk_Person. ID). However, the VALUE of the primary key is made up of TWO COLUMNS (P_Id + Last. Name). 17

SQL Primary Key Constraint To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD PRIMARY KEY (P_Id) To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE Persons ADD CONSTRAINT pk_Person. ID PRIMARY KEY (P_Id, Last. Name) To drop a PRIMARY KEY constraint, use the following SQL: ALTER TABLE Persons DROP PRIMARY KEY 18

What is Composite Primary Key? Primary key can be defined for a combination of columns. When a primary key consists of multiple columns, then it is called a Composite Primary Key. For a example a student having Department, Roll No and Name columns. If you presume that students of different departments can have same Roll. No then we will have to declare Department+Roll. No as the primary key. Roll. No for Department “BCS”: 1, 2, 3… Roll. No for Department “BBA”: 1, 2, 3… 19

SQL Foreign Key Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let's illustrate the foreign key with an example. Look at the following two tables: The "Persons" table: P_Id Last. Name First. Name Address City 1 Ahmad Khan Taimany Kabul 2 Svendson Tove Shar-e-naw Kabul 3 Pettersen Kari Jalalabad Ningarhar The "Orders" table: O_Id Order. No P_Id 1 77895 3 2 44678 3 3 22456 2 20

SQL Foreign Key Constraint Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table. The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to. 21

SQL Foreign Key Constraint The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created: CREATE TABLE Orders ( O_Id int NOT NULL, Order. No int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) 22

SQL Foreign Key Constraint To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL: ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE Orders ADD CONSTRAINT fk_Per. Orders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) To drop a FOREIGN KEY constraint, use the following SQL: ALTER TABLE Orders DROP FOREIGN KEY fk_Per. Orders 23

Default Constraint Ø Ø A column default can be specified using Default constraint. The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified, including NULL The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created: CREATE TABLE Persons ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255) DEFAULT ‘Kabul' ) 24

Default Constraint Ø The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE(): CREATE TABLE Orders ( O_Id int NOT NULL, Order. No int NOT NULL, P_Id int, Order. Date date DEFAULT GETDATE() ) 25

Default Constraint To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL: ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES' To drop a DEFAULT constraint, use the following SQL: ALTER TABLE Persons ALTER City DROP DEFAULT 26

Default Constraint Ø The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE(): alter table tbl. Order add Order. Date date CONSTRAINT FD_tbl. Order_Order. Date Default GETDATE() Ø Here we are adding a new column to the tbl. Order table having Default value i. e current date 27

Check constraint Ø Ø Ø CHECK constraint is used to limit the range of the values, that can be entered for a column. If you define a CHECK constraint on a single column it allows only certain values for this column. The following SQL creates a CHECK constraint on the “emp. Id" column when the “tbl. Emp" table is created. The CHECK constraint specifies that the column “emp. Id" must only include integers greater than 0. CREATE TABLE Persons ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0) ) 28

Check constraint Ø To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: CREATE TABLE tbl. Emp ( emp. Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), emp. Address varchar(255), emp. City varchar(255), CONSTRAINT chk_tbl. Emp CHECK (emp. Id>0 AND emp. City='Kabul') ) 29

Check constraint To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD CHECK (P_Id>0) To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL: The general formula for adding check constraint: ALTER TABLE {TABLE_NAME} ADD CONSTRAINT {CONSTRAINT_NAME} CHECK (BOOLEAN_EXPRESSION) If the BOOLEAN_EXPRESSION returns true, then the CHECK constraint allows the value, otherwise id doesn’t. Since, AGE is a nullable column, it’s possible to pass null for this column, when inserting a row. When you pass NULL for the AGE column, the Boolean expression evaluates to UNKNOWN, and allows the value. The general formula for dropping the check constraint: ALTER TABLE {TABLE_NAME} DROP CONSTRAINT {CONSTRAINT_NAME} 30

SQL RDBMS Databases My. SQL Ø Ø Ø My. SQL is an open source SQL database, which is developed by Swedish company My. SQL AB. My. SQL is pronounced "my ess-que-ell, " in contrast with SQL, pronounced "sequel. “ My. SQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X. Although it can be used in a wide range of applications, My. SQL is most often associated with web-based applications and online publishing. MS SQL Server Ø MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are: § § T-SQL. ANSI SQL. 31

SQL RDBMS Databases ORACLE Ø Ø Ø It is a very large and multi-user database management system. Oracle is a relational database management system developed by 'Oracle Corporation'. Oracle works to efficiently manage its resource, a database of information, among the multiple clients requesting and sending data in the network. It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, Net. Ware, Unix. Ware, OS/2 and most UNIX flavors. MS ACCESS Ø Ø Ø This is one of the most popular Microsoft products. Microsoft Access is an entry-level database management software. MS Access database is not only an inexpensive but also powerful database for small-scale projects. MS Access uses the Jet database engine, which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL). MS Access comes with the professional edition of MS Office package. MS Access has easyto-use intuitive graphical interface 32

SQL Syntax Ø SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax: Ø All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (; ). Ø Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but My. SQL makes difference in table names. So if you are working with My. SQL, then you need to give table names as they exist in the database. 33

SQL Syntax SQL SELECT Statement: Ø The SELECT statement is used to select data from a database. Ø The result is stored in a result table, called the result-set. SELECT column 1, column 2. . column. N FROM table_name; Ø The SELECT DISTINCT statement is used to return only distinct (different) values. Ø The WHERE clause is used to filter records. SELECT DISTINCT column 1, column 2. . column. N FROM table_name; SELECT column 1, column 2. . column. N FROM table_name WHERE CONDITION; 34

Operators and Wild Cards used with WHERE clause in a Select statement Operator Purpose = Equal to != or <> Not equal to > Greater than Operator Purpose >= Greater than or equal to % < Less than Specifies zero or more characters <= Less than or equal to - IN Specify a list of values Specifies exactly one character BETWEEN Specify a range [] LIKE Specify a pattern Any character within the brackets NOT Not in a list, range etc… [^] Not any character within the brackets 35

SQL AND & OR Operators The AND & OR operators are used to filter records based on more than one condition. The AND operator displays a record if both the first condition AND the secondition are true. The OR operator displays a record if either the first condition OR the secondition is true. You can also combine AND and OR (use parenthesis to form complex expressions). Ø SELECT * FROM Customers WHERE Country=‘Afghanistan' AND (City=‘Kabul' OR City=‘Herat'); 36

ORDER BY Keyword Ø The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SQL ORDER BY Syntax SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC; 37

ORDER BY Examples: The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column: SELECT * FROM Customers ORDER BY Country; The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column: SELECT * FROM Customers ORDER BY Country DESC; The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "Customer. Name" column: SELECT * FROM Customers ORDER BY Country, Customer. Name; The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "Customer. Name" column: SELECT * FROM Customers ORDER BY Country ASC, Customer. Name DESC; 38

INSERT INTO Statement Ø Ø Ø The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two forms. The first form does not specify the column names where the data will be inserted, only their values: INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ); Ø The second form specifies both the column names and the values to be inserted: INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); 39

UPDATE Statement Ø Ø The UPDATE statement is used to update existing records in a table. SQL UPDATE Syntax: UPDATE table_name SET column 1=value 1, column 2=value 2, . . . WHERE some_column=some_value; Ø Notice the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! 40

DELETE Statement Ø Ø The DELETE statement is used to delete rows in a table. SQL DELETE Syntax: DELETE FROM table_name WHERE some_column=some_value; Ø Ø Notice the WHERE clause in the SQL DELETE statement! The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted! It is possible to delete all rows in a table without deleting the table. DELETE FROM table_name; or DELETE * FROM table_name; 41

SQL Aliases SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable. SQL Alias Syntax for Columns: SELECT column_name AS alias_name FROM table_name; SQL Alias Syntax for Tables SELECT column_name(s) FROM table_name AS alias_name; 42

Alias Example for Table Columns The following SQL statement specifies two aliases, one for the Customer. Name column and one for the Contact. Name column. Note: It requires double quotation marks or square brackets if the column name contains spaces: SELECT Customer. Name AS Customer, Contact. Name AS [Contact Person] FROM Customers; In the following SQL statement we combine four columns (Address, City, Postal. Code, and Country) and create an alias named "Address": SELECT Customer. Name, Address+', '+City+', '+Postal. Code+', '+Country AS Address FROM Customers; 43

Alias Example for Tables The following SQL statement selects all the orders from the customer with Customer. ID=4 (abc). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we have used aliases to make the SQL shorter): SELECT o. Order. ID, o. Order. Date, c. Customer. Name FROM Customers AS c, Orders AS o WHERE c. Customer. Name=“abc" AND c. Customer. ID=o. Customer. ID; The same SQL statement without aliases: SELECT Orders. Order. ID, Orders. Order. Date, Customers. Customer. Name FROM Customers, Orders WHERE Customers. Customer. Name="Around the Horn" AND Customers. Customer. ID=Orders. Customer. ID; 44

SELECT TOP Clause The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. SELECT TOP Clause Syntax: SELECT TOP number|percent column_name(s) FROM table_name; SELECT TOP Example: SELECT TOP 2 * FROM Customers; SELECT TOP PERCENT Example: SELECT TOP 50 PERCENT * FROM Customers; 45

Group by ID Name Gender Salary Department 1 Ahmad Male 7000 IT 2 Akbar Male 10000 HR 3 Ajmal Male 3000 IT 4 Sara Female 7000 IT 5 Imran Male 30000 Finance Ø Ø Group by clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions. It is always used in conjunction with one or more aggregate functions. Department Total. Salary IT 17000 FINANCE 30000 HR 10000 SELECT Department, SUM(Salary) As Total. Salary FROM tbl. Employee GROUP By Department 46

Filtering Groups Grouping by multiple columns Using multiple aggregate functions Department Gender Salary Tot. Emp Finance Male 30000 1 HR Male 10000 1 IT Male 10000 2 IT Female 7000 1 SELECT Department, Gender, SUM(salary) as [Total Salary], COUNT(eid) as [No. of Employees] from emplyee group by Department, Gender order by Department DESC Ø WHERE clause is used to filter rows before aggregations, where as HAVING clause is used to filter groups after aggregations. The following queries produce the same result: Select Gender, Department, SUM(Salary) as Total. Salary from tbl. Employee Where Gender=‘Male’ GROUP BY Gender, Department Select Gender, Department, SUM(Salary) as Total. Salary from tbl. Employee GROUP BY Gender, Department Having Gender=‘Male’ 47

Difference: Where & Having Ø Ø Ø WHERE clause can be used with – Select, Insert and update statements, where as HAVING clause can only be used with the Select statement. WHERE filters rows before aggregation(GROUPING), where as, HAVING filters groups, after the aggregations are performed. Aggregate functions can not be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause, whereas, aggregate functions can be used in Having clause. 48