SQL Notes PDF

Chapter 4: SQL Content Overview of SQL Data Definition Commands (DDL) Integrity constraints: key constraints, Domain Constraints, Referential integrity , check constraints. Data Manipulation commands(DML) Data Control commands(DCL) Set and string operations aggregate function-group...

Chapter 4: SQL Content Overview of SQL Data Definition Commands (DDL) Integrity constraints: key constraints, Domain Constraints, Referential integrity , check constraints. Data Manipulation commands(DML) Data Control commands(DCL) Set and string operations aggregate function-group by, having, Views in SQL, joins. Nested and complex queries Triggers What is SQL?? SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, they are using different dialects, such as − MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version of SQL is called JET SQL (native format) etc. Why SQL? SQL is widely popular because it offers the following advantages − Allows users to access data in the relational database management systems. Allows users to describe the data. Allows users to define the data in a 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. Understanding important terminologies What is Schema? Design of a database is called the schema. The term "schema" refers to the organization of data as a blueprint of how the database is constructed. Schema is of three types: Physical schema, logical schema and view schema. The design of a database at physical level is called physical schema, how the data stored in blocks of storage is described at this level. Design of database at logical level is called logical schema. Design of database at view level is called view schema. In RDBMS database is represented as a collection of related tables. Another name for the table is Relation. Understanding Few terms (Contd..) What is DBMS Instance? The data stored in database at a particular moment of time is called instance of database. Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database. Datatypes in Postgre SQL Boolean Character Types [ such as char, varchar, and text] Numeric Types [ such as integer and floating-point number] Datatype - Boolean In PostgreSQL, the “bool” or”boolean” keyword is used to initialize a Boolean data type. These data types can hold true, false, and null values. Datatype - characters PostgreSQL has three character data types namely, CHAR(n), VARCHAR(n), and TEXT. CHAR(n) is used for data(string) with a fixed-length of characters with padded spaces. In case the length of the string is smaller than the value of “n”, then the rest of the remaining spaces are automatically padded. Similarly for a string with a length greater than the value of “n”, PostgreSQL throws an error. VARCHAR(n) is the variable-length character string. Similar to CHAR(n), it can store “n” length data. But unlike CHAR(n) no padding is done in case the data length is smaller than the value of “n”. TEXT is the variable-length character string. It can store data with unlimited length. Datatype - Numeric PostgreSQL has 2 types of numbers namely, integers and floating-point numbers. 1. Integer: Small integer (SMALLINT) has a range -32, 768 to 32, 767 and has a size of 2-byte. Integer (INT) has a range -2, 147, 483, 648 to 2, 147, 483, 647 and has a size of 4-byte. Serial (SERIAL) works similar to the integers except these are automatically generated in the columns by PostgreSQL. 2. Floating-point number: float(n) is used for floating-point numbers with n precision and can have a maximum of 8-bytes. float8 or real is used to represent 4-byte floating-point numbers. A real number N(d,p) meaning with d number of digits and p number of decimal points after, are part of numeric or numeric(d, p). These are generally very precise. Datatype- Temporal This data type is used to store date-time data. PostgreSQL has 5 temporal data type: DATE is used to store the dates only. TIME is used to stores the time of day values. TIMESTAMP is used to stores both date and time values. TIMESTAMPTZ is used to store a timezone-aware timestamp data type. INTERVAL is used to store periods of time. Data types/ Domain types in SQL Numeric Data Types ATA TYPE FROM TO bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 int -2,147,483,648 2,147,483,647 smallint -32,768 32,767 tinyint 0 255 bit 0 1 decimal -10^38 +1 10^38 -1 numeric -10^38 +1 10^38 -1 money -922,337,203,685,477.5808 +922,337,203,685,477.5807 smallmoney -214,748.3648 +214,748.3647 Date and time datatype DATA TYPE FROM TO float -1.79E + 308 1.79E + 308 real -3.40E + 38 3.40E + 38 DATA TYPE FROM TO datetime Jan 1, 1753 Dec 31, 9999 smalldatetime Jan 1, 1900 Jun 6, 2079 date Stores a date like June 30, 1991 time Stores a time of day like 12:30 P.M. Char/string data type Sr.No. DATA TYPE & Description char 1 Maximum length of 8,000 characters.( Fixed length non-Unicode characters) varchar 2 Maximum of 8,000 characters.(Variable-length non-Unicode data). varchar(max) 3 Maximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only). text 4 Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters. DDL- Data Definition Language DML – Data Manipulation Language DCL – Data Control Language TCL – Transaction Control Language  DDL Command Create, Alter, Drop, Rename, truncate Data Definition Language (DDL) Data Definition Language (DDL) is used to define the database structure or schema. It allows the specification of not only a set of relations (tables) but also information about each relation, including: The schema for each relation(or table). The domain of values associated with each attribute. Integrity constraints. The set of indices to be maintained for each relations. Security and authorization information for each relation. The physical storage structure of each relation on disk. CREATE statement Major CREATE statements: CREATE SCHEMA (or DATABASE) –Defines a portion of the database owned by a particular user CREATE TABLE–defines a new table and its columns CREATE VIEW–defines a logical table from one or more tables or views CREATE DATABASE Example Syntax: CREATE DATABASE database_name; Example: CREATE DATABASE Customers_DB; Create Table Construct – Create command Syntax: CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype,.... ); Example: 1) create table customers (cust_ID smallint, cust_name varchar(45),email varchar(45)); 2) create table branch (branch_name char(15) not null, branch_city char(30), assets integer) Integrity Constraints in Create Table not null primary key (A1,..., An ) Example: Declare branch_name as the primary key for branch. create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name)); OR create table branch (branch_name char(15) primary key , branch_city char(30), assets integer); primary key declaration on an attribute automatically ensures not null in SQL-92 onwards, needs to be explicitly stated in SQL-89 DROP COMMAND The drop table command deletes all information about the dropped relation from the database. Syntax : drop table table_name; Example: drop table branch; ALTER TABLE COMMAND The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column Consider following table: ID LastName FirstName Address City CREATE TABLE Persons ( 1 Hansen Ola Timoteivn Sandnes PersonID int, 10 LastName varchar(255), FirstName varchar(255), 2 Svendson Tove Borgvn 23 Sandnes Address varchar(255), 3 Pettersen Kari Storgt 20 Stavanger City varchar(255) ); Syntax to add column: ALTER TABLE table_name ADD column_name datatype; Example: ALTER TABLE Persons ADD Email varchar(255); All tuples in the relation are assigned null as the value for the new attribute. ALTER TABLE - DROP COLUMN Syntax: ALTER TABLE table_name DROP COLUMN column_name; Example: ALTER TABLE Person DROP COLUMN email; ALTER TABLE - ALTER/MODIFY COLUMN PostGreSql: ALTER TABLE table_name ALTER COLUMN coumn_name TYPE datatype; SQL Server / MS Access: ALTER TABLE table_name MODIFY COLUMN column_name datatype; My SQL / Oracle (prior version 10G): ALTER TABLE table_name MODIFY COLUMN column_name datatype; ID LastName FirstName Address City 1 Hansen Ola Timoteivn Sandnes 10 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 1) add a column named "DateOfBirth" in the "Persons" table. ALTER TABLE Persons ADD DateOfBirth date; 2) change the data type of the column named "DateOfBirth" in the "Persons" table. PostGreSQL ALTER TABLE Persons ALTER COLUMN DateOfBirth TYPE year; MySQL ALTER TABLE Persons MODIFY COLUMN DateOfBirth year; 3) delete the column named "DateOfBirth" in the "Persons" table. ALTER TABLE Persons DROP COLUMN DateOfBirth;  ALTER TABLE - Modifying Multiple columns ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition, ALTER COLUMN column_name TYPE column_definition,... ; EXAMPLE: ALTER TABLE order_details ALTER COLUMN notes TYPE varchar(500), ALTER COLUMN quantity TYPE numeric ALTER TABLE - Renaming a table ALTER TABLE table_name RENAME TO new_table_name; CREATE TABLE vendors ( id serial PRIMARY KEY, name VARCHAR NOT NULL ); Rename above table to suppliers ALTER TABLE vendors RENAME TO suppliers; ALTER TABLE - Renaming a column ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; OR Note: Here COLUMN keyword is optional ALTER TABLE table_name RENAME column_name TO new_column_name; Example: ALTER TABLE customers RENAME COLUMN email TO contact_email; DML Commands DML – Data Manipulation Language DML includes… Insert Select Update delete Inserting values in table – INSERT INTO statement Syntax: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); Example: Insert into Persons Values (1001,’Desai’, ’Rishabh’, ’Mumbai’); Insert into Persons Values (1002,’Shah’, ‘Mani,’Mumbai’); Delete command Delete command is used to delete all records in a given table. Note that only records are deleted , table still exists. DELETE FROM table_name WHERE condition; Example: DELETE FROM customers; // all records will be deleted DELETE FROM customers WHERE cust_city = ‘Mumbai’; // only records of Mumbai city deleted Reading data from user and inserting into table Insert into Persons Values (&PersonID,’&Lastname’,’&FirstName’,’&Address’, ‘&City’); Insert Data Only in Specified Columns Insert into Persons (PersonID, LastName,FirstName) Value (1001,’Desai’, ‘Rishabh’) Insert into Persons (PersonID, LastName,FirstName) Value (1001,’Shah’, ‘Mani’) THE SELECT CLAUSE The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. Syntax: SELECT column1, column2,... FROM table_name; Example: SELECT * from Persons; Will select all the columns and all the rows from table Persons SAMPLE DATABSE CustomerI CustomerN ContactNa Address City PostalCode Country D ame me 1 Alfreds Maria Obere Str. Berlin 12209 Germany Futterkiste Anders 57 2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico Emparedad Constitució D.F. os y n 2222 helados 3 Antonio Antonio Mataderos México 05023 Mexico Moreno Moreno 2312 D.F. Taquería 4 Around the Thomas 120 London WA1 1DP UK Horn Hardy Hanover Sq. 5 Berglunds Christina Berguvsvä Luleå S-958 22 Swede snabbköp Berglund gen 8 Selecting specified columns only Select CustomerID, CustomerName, ContactName From customers; Will display only specified columns. THE SELECT CLAUSE (Cont.) SQL allows duplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. Find the names of all country in the customer relations, and remove duplicates. SELECT DISTINCT Country FROM Customers; The keyword all specifies that duplicates not be removed. select all country from Customers; SELECT COUNT(DISTINCT Country) FROM Customers; Will give you count of distinct countries in Customer table THE SELECT CLAUSE(Cont.) An asterisk in the select clause denotes “all attributes” select * from Customers; The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or attributes of tuples. The query: select loan_number, branch_name, amount * 100 from loan would return a relation that is the same as the loan relation, except that the value of the attribute amount is multiplied by 100.  THE WHERE CLAUSE The where clause specifies conditions that the result must satisfy SYNTAX: SELECT column1, column2,... FROM table_name WHERE condition; EXAMPLE: SELECT * FROM Customers WHERE Country='Mexico’; OR SELECT * FROM Customers WHERE Country like 'Mexico’; WHERE CLAUSE with AND ,OR ,NOT SYNTAX: SELECT column1, column2,... FROM table_name WHERE condition1 AND condition2 AND condition3...; EXAMPLE: SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; SELECT * FROM Customers WHERE City='Berlin' OR City='München'; SELECT * FROM Customers WHERE NOT Country='Germany'; OPERATORS in WHERE CLAUSE Operator Description = Equal > Greater than < Less than >= Greater than or equal = 90000) and (salary