Intorduction SQL

1. Intorduction SQL Prepared BY: Mitali Sonar (Assistant Professor) Prepared By: Mitali Sonar (Assistant Professor)

2. What is database? • Collection of information/ meaningful data • Ex:- postal address • Values in this fields are data • Address book is database Prepared By: Mitali Sonar (Assistant Professor)

3. What is DBMS? • Software that designs, manages database • DBMS allows insert, update, delete and processing of data in database • Ex:- • Oracle • Ingres • Dbase • MS. Access Prepared By: Mitali Sonar (Assistant Professor)

4. What is relational DBMS • DBMS based on relational model • Stores data in form of related data/tables • Dbase can Be viewed in many different ways • Ex:- • Oracle • Microsoft SQL server • Sybase SQL Server • DB2 • MySQL Prepared By: Mitali Sonar (Assistant Professor)

5. DBMS Vs. RDBMS Prepared By: Mitali Sonar (Assistant Professor)

6. Introduction to SQL • Structured Query Language • Provides interface to RDBMS • Language for communication with oracle server to access data • Category/ component of SQL • DDL commands (data definition language) • To create a database objects not data • Define relation schema ,deleting relations and modifying relational schemas • Ex:- • CREATE :- Used to create a db object • ALTER :- alter structure of DB • DROP:- Delete the object from DB • TRUNCATE :- Remove all records from DB Prepared By: Mitali Sonar (Assistant Professor)

7. Introduction to SQL (conti…) • DML commands (Data Manipulation Language) • To manipulate data of a database objects • Includes commands to insert tuples into , delete tuples from and modify tuples in database. • INSERT :- Insert data into table • UPDATE:-Updates existing data within table • DELETE :- deletes all records from table • DQL command (Data Query Language) • To retrieve the data from a database • Getting data from database and imposing ordering upon it • SELECT:- retrieve data from the DB Prepared By: Mitali Sonar (Assistant Professor)

8. Introduction to SQL (conti…) • DCL commands (Data Control Language) • Controlled access to data • GRANT - Used to give privilege to user on object • REVOKE - Used to withdraw the privilege that has been granted to the user. • COMMIT:- save work done • ROLLBACK :- restore the database to original state since last commit Prepared By: Mitali Sonar (Assistant Professor)

9. Basic data type • char(size) • A fixed length character string with only 255 user specified characters • Data held is right padded with spaces to whatever size is specified • varchar2(size) • A variable character length string with alphanumeric values • maximum length 4000 characters • Inserted values are not contains white spaces • Date • a calendar date containing a year, month and day of the month • DD-MON-YYYY • Number (P,S) • Used to store a number (fixed or floating point). • Max precision can be 38 digits • P – max length of the data • S- number of places to the right of decimal Prepared By: Mitali Sonar (Assistant Professor)

10. TABLE fundamentals • Table holds user data & Similar to a spreadsheet • Consist of rows (tuples) & columns (attribute) • Each column have data type associated with it. • Syntax :- CREATE TABLE table_name (col1 datatype(size), …., coln datatype(size) ); OUTPUT:- Prepared By: Mitali Sonar (Assistant Professor)

11. Inserting data into tables • Loads the value passed by SQL insert command into columns specified • INSERT INTO tablename VALUES(<val 1>,<val 2>…….<valn>); Prepared By: Mitali Sonar (Assistant Professor)

12. Viewing data in the tables • SELECT – is used to retrieve rows from one or more tables • All rows & all columns • SELECT * FROM tablename; • Ex: - show all employee details Prepared By: Mitali Sonar (Assistant Professor)

13. Viewing data in the tables (cont..) • Selected columns & all rows • SELECT Column1, Column2 FROM tablename; Prepared By: Mitali Sonar (Assistant Professor)

14. Viewing data in the tables (cont..) • Selected Rows and all Columns • SELECT * FROM tablename WHERE condition • Ex:- Display details of employee who lives in andheri area • Ex:- Display details of employee who live in vile parle area Prepared By: Mitali Sonar (Assistant Professor)

15. Viewing data in the tables (cont..) • Selected Rows and selected Columns • SELECT Column1, Column2 FROM tablename WHERE condition • Ex:- list eno and ename who lives in vile parle area Prepared By: Mitali Sonar (Assistant Professor)

16. Eliminate duplicate values • DISTINCT • It scans through the values of columns specified and display only unique values among them • SELECT DISTINCT column1 FROM tablename; Prepared By: Mitali Sonar (Assistant Professor)

17. Sorting the data • ORDER BY :-retrieves rows from table either ascending or descending order • SELECT * FROM tablename ORDER BY colname (sortorder); • Ex:- show details of employee according to department name. Prepared By: Mitali Sonar (Assistant Professor)

18. DELETE operation • Deletes rows from table that satisfies the condition provided by where clause • Removal of all rows form the table DELETE FROM tablename; • Removal of specific rows DELETE FROM tablename where Condition; Prepared By: Mitali Sonar (Assistant Professor)

19. Update the content of table • Change or modify the data values in table • Updating all rows • UPDATE tablename SET col1 = expression1; • Ex:- update the department name to research for all employees Prepared By: Mitali Sonar (Assistant Professor)

20. Update the content of table • Change or modify the data values in table • Updating rows conditionally • UPDATE tablename SET col1 = expression1 WHERE condition; • Ex:- update the address of employee sanket from sion to navi mumbai NEW TABLE OLD TABLE Prepared By: Mitali Sonar (Assistant Professor)

21. Modifying the structure of the tables • ALTER TABLE :-Changes the structure of existing table • Add, delete columns / changes the datatype of columns /rename the columns or table itself • Adding a new column ALTER TABLE tablename ADD (new_column _name datatype(size)); Ex:- Enter a new field called contactno in table Prepared By: Mitali Sonar (Assistant Professor)

22. Modifying the structure of the tables(cont..) • Dropping a column from a table ALTER TABLE tablename DROP COLUMN column name; Ex- drop the column contactno from the employee table Prepared By: Mitali Sonar (Assistant Professor)

23. Modifying the structure of the tables(cont..) • Modifying Existing columns ALTER TABLE tablename MODIFY (colnname newdatatype (newsize)); Ex:- Change the employee name field to hold Maximum 30 characters Prepared By: Mitali Sonar (Assistant Professor)

24. Restriction to ALTER TABLE • Following task can’t be performed using ALTER TABLE • Change the name of the table /columns • Decrease size of column • RENAME TABLES • REANAME tablename to newtablename; • Ex:- change the name of table to emp_data; • RENAME employee_data TO emp_data; Prepared By: Mitali Sonar (Assistant Professor)

25. TRUNCATING TABLES • Empties a table completely • Different from delete in following ways • Drop and recreates the table that is much faster than deleting • Truncate are not transaction-safe • No. of deleted rows are not returned TRUNCATE TABLE tablename; • Ex:- clear all records from employee table • Output- • DESTROYING TABLES • When table becomes obsolete and needs to be discarded. • Destroys a specific table DROP TABLE tablename; • Ex:-DROP TABLE emp_data; • O/P :- table dropped. Prepared By: Mitali Sonar (Assistant Professor)

26. Examining objects created by users • Finding out tables created by user • SELECT table_name FROM user_tables; • Displaying the table structure • To display information about columns defined in a table • DESCRIBE tablename; Prepared By: Mitali Sonar (Assistant Professor)

27. Data constraints • Set of rules that must be applied to data prior to being stored in DB • Ex:- no employee in sales department should have salary more than 10,000 • If there is any violation between the constraint and the data action, the action is aborted by the constraint. • Once constraints is attached to table any SQL INSERT or UPDATE statement automatically causes these constraints to be applied to data before its being stored in table • 2 types • I/O constraint • Business rule constraints Prepared By: Mitali Sonar (Assistant Professor)

28. Data constraints • In SQL, we have the following constraints: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT Prepared By: Mitali Sonar (Assistant Professor)

29. I/O Constraint PRIMARY KEY CONSTRAINTS • One or more column in table that uniquely identify each tuple • No primary key column can contain NULL value • primary key column is a mandatory column • Data held across column must be unique. • Single column primary key is Simple key • A multi column primary key is a Composite primary key • Defined either in CREATE TABLE statement or in ALTER TABLE statement Prepared By: Mitali Sonar (Assistant Professor)

30. PRIMARY KEY CONSTRAINTS • Syntax : Column-name datatype(size) primary key • Ex: CREATE TABLE Employee( ENO number(4) primary key, ENAME varchar2(20)); • PRIMARY KEY (column-name1, column-name2) • Ex:CREATE TABLE Employee( ENO number(4), ENAME varchar2(20), CONSTRAINT pk_ENO primary key (ENO)); • EX:- ALTER TABLE EmployeeADD CONSTRAINT pk_ENO primary key (ENO)); Prepared By: Mitali Sonar (Assistant Professor)

31. Foreign key constraints • Represents relationship between two tables • Its value is derived from primary key of the other table • Table in which foreign key is defined is called foreign table • Table in which primary key is defined is called master table • Syntax :- REFERENCES tablename.columnname • Features • Parent that is being referenced has to be unique • Child may have duplicate • Parent record can be deleted provided no child exists • Master table cannot be updated if child record exists Prepared By: Mitali Sonar (Assistant Professor)

32. Foreign key constraints • create table branch(branch_name char(15),branch_city char(30),assets number(12,2),primary key (branch_name )); • create table account(account_number char(10),branch_name char(15),balance integer,primary key (account_number), references branch (branch_name) ); Prepared By: Mitali Sonar (Assistant Professor)

33. Foreign key constraint • Insert/update operations in foreign • A value cannot be inserted in foreign table if corresponding value is not presented in master table Prepared By: Mitali Sonar (Assistant Professor)

34. Foreign key constraints • Delete operation on primary key table(master table) • Oracle display error message when deleting a record form master table and corresponding record exists in foreign key table • This will prevent the delete operation form execution Prepared By: Mitali Sonar (Assistant Professor)

35. ON DELETE CASCADE • If a record is deleted in master table , all corresponding records in foreign key table is also deleted. • create table "branch" ( `"branch_name" char(15), "branch_city" char(30), "assets" number(12,2), constraint"branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE CASCADE ENABLE ) Prepared By: Mitali Sonar (Assistant Professor)

36. Prepared By: Mitali Sonar (Assistant Professor)

37. ON DELETE SET NULL • If a record is deleted from Master table, the value held by foreign key table is set to Null • The records in foreign key table will not deleted • create table "branch" ( "branch_name" char(15), "branch_city" char(30), "assets" number(12,2), constraint"branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE SET NULL) Prepared By: Mitali Sonar (Assistant Professor)

38. Unique key constraint • It permits multiple entries of NULL into column • It will not allowed duplicate value • A table can have more than one unique key that is not possible for primary key • ALTER TABLE customerADD CONSTRAINT Cust_con UNIQUE (cust_no); Prepared By: Mitali Sonar (Assistant Professor)

39. BUSINESS RULE CONSTRAINT • Rules are applied to data prior the data is being inserted • This will ensure that data in table have integrity • Ex;- Rule ” no employee in company shall get a salary less than 10000” • Inserted to column or table using create table or alter table • Constraints can be defined at • Column level • Table level Prepared By: Mitali Sonar (Assistant Professor)

40. NULL VALUE CONCEPTS • Some fields do not have values because information is not available at time of data entry • Will place NULL value that is different from blank(if data type is character) or zero(if data type is number) • Null can be inserted in columns of any data type • If a column has NULL value than oracle ignore UNIQUE, FOREIGN KEY, CHECK constraints • NOT NULL concept • Ensures that column cannot left empty • Only be applied at column level • If column is defined as not null, that column becomes a mandatory column Prepared By: Mitali Sonar (Assistant Professor)

41. CHECK CONSTRAINT • used to limit the value range that can be placed in a column. • Specified as logical expression that evaluates as TRUE or FALSE • CHECK constraint on a single column • allows only certain values for this column. • CHECK constraint on a table • can limit the values in certain columns based on values in other columns in the row. • CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),CONSTRAINT chk_Person CHECK (P_Id>0 and City=‘Ahmedabad')) Prepared By: Mitali Sonar (Assistant Professor)

42. Create table customer_master ( cust_no varchar2(5), Fname varchar2(10), Lname varchar2(10), CHECK (cust_no like ‘C%’), CHECK (Fname =UPPER (FNAME)) ); If condition has false value error message will be displayed and processing stops there Prepared By: Mitali Sonar (Assistant Professor)

43. Default Constraint • 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. • CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) DEFAULT ‘ahmedabad'); Prepared By: Mitali Sonar (Assistant Professor)

44. Create the tables described as follow • Table name: Product Master Prepared By: Mitali Sonar (Assistant Professor)

45. Computations done on table data • manipulates individual data items and returns a result. • Ex:- display all employee’s detail with their annual salary (salary *12) • Arithmetic operators Prepared By: Mitali Sonar (Assistant Professor)

46. Customer Table SELECT cname, opening_amt, receive_amt, (opening_amt + receive_amt)   FROM customer   WHERE (opening_amt + receive_amt)>15000;  Prepared By: Mitali Sonar (Assistant Professor)

47. Renaming the column used with expression • SQL aliases are used to give a column in a table, a temporary name. • SELECT column_name AS alias_name FROM table_name; Prepared By: Mitali Sonar (Assistant Professor)

48. SELECT C_Name, Address+City+PostalCode+CountryAS AddressFROM Customers; Prepared By: Mitali Sonar (Assistant Professor)

49. Logical operators • Logical operators in SQL will return either true or false value. Prepared By: Mitali Sonar (Assistant Professor)

50. "OR" Logical Operator: • find the details of students who are studying either Maths or Science, the query would be like, • SELECT * FROM studentdetails WHERE sub1 = 'Maths' OR sub1= 'Science' Prepared By: Mitali Sonar (Assistant Professor)