SQL

S tructured Q uery L anguage- SQL4.0 SQL : S tructured Q uery L anguage4.1.SQL4.2 Data definition language:4.3 Database modification operations4.3.1. ALTER TABLE4.3.2 DROP TABLE4.3.3 Index4.3.4 View4.4 Data manipulation Language4.4.1 Insertion of Records:4.4.2 Deletion of Records4.4.3 Updates of records4. 5 Data Manipulation - SELECT4.5.1 SELECT – FROM :4.5.2 SELECT .. FROM …. WHERE4.5.3 ORDER BY4 .5.4 Character / Pattern Matching4.5.5 IN and BETWEEN4.6. Built-in Functions4.7 GROUP BY4.8. HAVING clause4.9 Calculations with select Statements4.10 Removing Duplicate Rows4.11 SQL – Reserved Word4.0 S tructured Q uery L anguage (also known "sequel").SQL was developed at IBM (San Jose Lab) during the 1970's, and standardised during the 1980's.The current standard implemented by commercial DBMSs is SQL2 (aka SQL-92). Most RDBMSs extend the standard language in various "useful" ways.A new standard (SQL3) is imminent, to extend the language with triggers, recursion, objects.SQL: S tandard SQL2:SQL is an ANSI/ISO standard language for querying and manipulating relational DBMSs. It is used1as a data definition language-data definition facilities-database modification operations2as an update language- database update operations ( insert, delete and modify)3) as a query language-database query applications-aggregation operations4.2 Data definition language:-data definition facilities-database modification operationsData Definition CREATE TABLE CommandA RELATION in relational database , table , consists of attributes (column) and contains tuples (records ) of data. When creating a table, the user needs to specifyeach column by name and assign a data type for each column.The syntax for defining (or creating) a table is specified below.CREATE TABLE < [owner.]table_name >( <column_name datatype [DEFAULT expression] [column_constraint]> , . ..............[<column_name datatype [DEFAULT expression] [column_constraint] ],[table_constraint] [ENABLE enable_clause] [DISABLE disable_clause][ AS subquery].The column_constraint could be defined in many ways. The following example illustrates some of the ways in defining the column_constraint besides illustrating the CREATE TABLE command.Note that the columns may be NULL columns or NOT NULL columns. If specified as a NULL column, the specified column can take on NULL values. Refer class notes for more information!.SQL> CREATE TABLE STUDENT( student_ id char(15)Student_name varchar2(50) NOT NULLCONSTRAINT student_pkPRIMARY KEY,);SQL> CREATE TABLE COURSE( course_id char(10) NOT NULL,section_id char(12) NOT NULL,Course_name varchar2(50) NOT NULLCONSTRAINT course_pkPRIMARY KEY (course_i, section_id),);SQL> CREATE TABLE enroll(student_id char(15) NOT NULL,course_id char(10) NOT NULL,section_id char(12) DEFAULT '01' NOT NULL,semester char(4) DEFAULT 'F97' CONSTRAINT sem_nn NOT NULL, grade char(1) NULL,CONSTRAINT enroll_pk PRIMARY KEY(student_id, course_id, section_id), CONSTRAINT enroll_fk1 FOREIGN KEY (student_id) REFERENCESstudent,CONSTRAINT enroll_fk2 FOREIGN KEY (course_id, section_id)REFERENCES course,CONSTRAINT grade_constraint CHECK (grade in ('A','B','C','D','E', 'I' )) ); Data types in OracleFollowing are some useful data typesCHAR(n) - character string – n characters – default 1 VARCHAR2(n) or VARCHAR(n) - variable characters – maximum characters n NUMBER( w, d) - Floating point number – width w number, d digits after decimal pointsNUMBER(n) - n digit integer or numbersDATE - day/month/year4.3 Database modification operations4.3.1. ALTER TABLE CommandThe purpose of the ALTER TABLE command is one/more of the following: ∙To add a column to an existing table∙To add a new integrity constraint to an existing table∙To redefine a column (data type, size, default value)∙To enable, disable, or drop an integrity constraint or trigger.∙To modify storage characteristics or other parameters.The General Syntax of Alter Table CommandALTER TABLE [owner.]<table_name>{ [ADD (<column_name> <datatype> [DEFAULT expr][<column_constraint>] ) ([table_constraint] ) ][MODIFY (<column_name> <datatype> [DEFAULT expr][<column_constraint>] )] [DROP <drop_clause>][ENABLE <enable_clause>] [DISABLE <disable_clause>] }At least one of the above list (ADD, MODIFY, DROP, ENABLE, or DISABLE) must be specified.Oracle does not permit the deletion of a column or changing the name of a column. These can be done only by dropping and recreating the table and its definitions. SQL> ALTER TABLE EMPLOYEEADD (no_children char(15));Adding an additional attribute no_children to employee table4.3.2 DROP TABLE CommandPermits users to drop an existing table. The execution of this command results in the removal of the data stored in that table as well. The syntax for the DROP TABLE command:DROP TABLE [owner.]<table_name>4.3.3 IndexAn INDEX is a database object that contains an entry for each value that appears in the indexed column(s) of the table. It provides direct and faster access to rows of data. CREATE INDEX [owner.]<index_name>ON [owner.]<table_name> ( <column_name> [ASC | DESC] )This statement creates a single index on those specified columns. If the user wishes to create a separate index for each of many different columns, each index definition must be specified as a separate command. The default order in the index is the ascending order (ASC).An index can be dropped using the DROP INDEX command:DROP INDEX [owner.]<index_name>4.3.4 ViewA VIEW is a logical table that allows the users to access data from multiple other tables and/or views. The view contains no data. The data for the view comes from different other tables (or views) which are called the base table(s) for this view.The syntax for the CREATE VIEW command:CREATE VIEW [owner.]<view_name>(<column_list>)AS <subquery>SQL> CREATE VIEW EMPSALAS SELECT ename, sal*12 Annual_SalaryFROM empWHERE deptno = 20;This view, EMPSAL would now have 2 columns, ename and Annual_Salary. Note: the column Annual_Salary does not exist in the base table employee. It is a derived value, derived by the product of the employee’s salary (sal) and 14. This derived column is a liased as "Annual_Salary" in the query.An existing VIEW can be removed from the database by dropping it.DROP VIEW [owner.]<view_name>EXERCISE 1: Creating a COMPANY DATABASE- Creation of Tablescreate table employee (fname char(15) not null,minit char(1),lname char(15) not null,ssn char(9) not null,bdate date,address char(30),sex char(1) check (sex in ('m','f')),salary number(10,2),superssn char(9),dno number(2),primary key (ssn),constraint employee_superforeign key (superssn) references employee(ssn) ); create table dependent (essn char(9) not null,dependent_name char(15) not null,sex char(1) check (sex in ('m','f')),bdate date,relationship char(8),primary key (essn, dependent_name),constraint dependent_employeeforeign key (essn) references employee(ssn) ); create table department (dname char(15) not null,dnumber number(2) not null,mgrssn char(9),mgrstartdate date,primary key (dnumber),unique (dname),constraint department_employeeforeign key (mgrssn) references employee(ssn) ); alter table employee add(constraint employee_departmentforeign key (dno) references department(dnumber)); create table project (pname char(15) not null,pnumber char(3) not null,plocation c har(15),dnum number(2) not null,primary key (pnumber),unique (pname),constraint proj_deptforeign key (dnum) references department(dnumber) ); create table dept_locations (dnumber number(2) not null,dlocation c har(15) not null,primary key (dnumber,dlocation),constraint dept_loc_departmentforeign key (dnumber) references department(dnumber) ); create table works_on (essn char(9) not null,pno char(3) not null,hours number(3,1),primary key (essn, pno),constraint works_on_employeeforeign key (essn) references employee(ssn) ,constraint works_on_projectforeign key (pno) references project(pnumber) );4.4 Data manipulation Language (DML) commandsThere are four data manipulation language (DML) statements:INSERT, UPDATE and DELETE and SELECT.The statementsi) insert intoii)delete andiii)Updateare required to add new records into the relations , delete unnecessary records and modify certain attributes of a record.4.4.1 Insertion of Records:The Syntax of INSERT INTO operation:INSERT INTO Relation VALUES(val1, val2, val3, ...)Example: Add Sample Rrecords into the tables created in the exerciseExample 2: INSERTING RECORDSTable Department:insert into department values('Research', 5, null, '22-MAY-78');insert into department values('Administration',4, null,'01-JAN-85');insert into department values('Headquarters', 1, null, '19-JUN-71');Table employee:insert into employee values('James', 'E', 'Borg', '888665555', '10-NOV-27', '450 Stone, Houston, TX', 'm', 55000, null, 1);insert into employee values('Franklin', 'T', 'Wong', '333445555', '08-DEC-45','638 Voss, Houston, TX', 'm', 40000, '888665555', 5);insert into employee values ('John', 'B', 'Smith', '123456789', '09-JAN-55', '731 Fondren, Houston, TX', 'm', 30000, '333445555', 5);insert into employee values ('Jennifer', 'S', 'Wallace', '987654321', '20-JUN-31', '291, Berry, Bellaire, TX', 'f', 43000, '888665555', 4);insert into employee values('Alicia', 'J', 'Zelaya', '999887777', '19-JUL-58', '3321 Castle, Spring, TX', 'f', 25000, '987654321', 4);insert into employee values('Ramesh', 'K', 'Narayan', '666884444', '15-SEP-52', '975 Fire Oak, Humble, TX', 'm', 38000, '333445555', 5);insert into employee values('Joyce', 'A', 'English', '453453453', '31-JUL-62', '5631 Rice, Houston, TX', 'f', 25000, '333445555', 5);insert into employee values('Ahmad', 'V', 'Jabbar', '987987987', '29-MAR-59', '980 Dallas, Houston, TX', 'm', 25000, '987654321', 4);Table : dep_locationsinsert into dept_locations values (1, 'Houston');insert into dept_locations values (4, 'Stafford');insert into dept_locations values (5, 'Bellaire');insert into dept_locations values (5, 'Sugarland');insert into dept_locations values (5, 'Houston');Table Project:insert into project values ('ProductX', 1, 'Bellaire', 5);insert into project values ('ProductY', 2, 'Sugarland', 5);insert into project values ('ProductZ', 3, 'Houston', 5);insert into project values ('Computerization', 10, 'Stafford', 4);insert into project values ('Reorganization', 20, 'Houston', 1);insert into project values ('Newbenefits', 30, 'Stafford', 4);Table Works_on :insert into works_on values ('123456789', 1, 34.5);insert into works_on values ('123456789', 2, 7.5);insert into works_on values ('666884444', 3, 40.0);insert into works_on values ('453453453', 1, 20.0);insert into works_on values ('453453453', 2, 20.0);insert into works_on values ('333445555', 2, 10.0);insert into works_on values ('333445555', 3, 10.0);insert into works_on values ('333445555', 10, 10.0);insert into works_on values ('333445555', 20, 40.0);insert into works_on values ('999887777', 30, 30.0);insert into works_on values ('999887777', 10, 10.0);insert into works_on values ('987987987', 10, 35.0);insert into works_on values ('987987987', 30, 5.0);insert into works_on values ('987654321', 30, 20.0);insert into works_on values ('987654321', 20, 15.0);insert into works_on values ('888665555', 20, null);Table Dependent:insert into dependent values('333445555','Alice','f','05-APR-76', 'DAUGHTER'); insert into dependent values('333445555', 'Theodore', 'm', '25-OCT-73', 'SON'); insert into dependent values('333445555', 'Joy', 'f', '03-MAY-48', 'SPOUSE'); insert into dependent values('987654321', 'Abner', 'm', '29-FEB-32', 'SPOUSE'); insert into dependent values('123456789', 'Michael', 'm', '01-JAN-78', 'SON'); insert into dependent values ('123456789', 'Alice', 'f', '31-DEC-78','DAUGHTER');insert into dependent values ('123456789', 'Elizabeth', 'f', '05-MAY-57','SPOUSE');4.4.2 Deletion of RecordsThe Syntax of the DELETE operation:DELETE FROM RelationWHERE ConditionRemoves all tuples from Relation that satisfy Condition.DELETE FROM dependentWHERE essn = '123456789' ;A record from the relation dependent where essn = ‘123456789’is removed/Special case:DELETE FROM R;All the records in the relation R are removedNOTE: BE CAREFUL when using DELETE operation. If the Where condition is not properly defined, unwanted deletion of records mat takes place4.4.3 Updates of recordsAn update allows you to modify values of specified attributes in specified tuples of a relation:The SyntaxUPDATE RSET list of assignmentsWHERE ConditionUPDATE departmentSET mgrssn = '333445555'WHERE dnumber = 5;UPDATE departmentSET mgrssn = '987654321'WHERE dnumber = 4;UPDATE departmentSET mgrssn = '888665555'where dnumber = 1;4. 5 Data Manipulation - SELECTThe purpose of the SELECT statement is to retrieve data from one or more tables or views.The syntax for the SELECT statement is as follows:SELECT [DISTINCT | ALL ] <select_list>[FROM <table> [ { ,<table> }...] ][WHERE <condition> ][AND | OR <more_conditions>][GROUP BY <expression>][HAVING <condition>][ORDER BY [<column(s)> | <position>][ASC | DESC] ][ FOR UPDATE [OF [<username>.]<table_name>.<column>] ]Examples of SELECT statement in the Order of increasing complexityExample 3:EmployeeFname minit lname ssn bdate address sex salary Supper_ssn dno James E Borg 888665555 10-NOV-27 450 Stone Houston TX m 55000 null 1 Franklin T 'Wong' 333445555 08-DEC-45 638 Voss Houston TX m 40000 888665555 5John B Smith 123456789 09-JAN-55 731Fondren Houston TX m 30000 333445555 5Jennifer S Wallace 987654321 20-JUN-31 291 Berry Bellaire TX f 43000 888665555 4Alicia J Zelaya 999887777 19-JUL-58 3321 Castle Spring TX f 25000 987654321 4Ramesh K Narayan 66688444 15-SEP-52 975Fire Oak Humble TX m 38000 333445555 5 Joyce A English 453453453 31-JUL-62 5631 Rice Houston TX f 25000 33344555 5 Ahmad’v Jabbar 987987987 29-MAR-59 980 Dallas Houston TX m 25000 987654321 4Table : dep_locations4.5.1 SELECT – FROM :SQL> SELECT *FROM Employee;retrieve all the columns and the records the table employee SQL> SELECT ssn, salaryFROM Employee;Only two columns are selectedssn salary888665555 55000333445555 40000123456789 30000987654321 43000999887777 2500066688444 38000453453453 25000987987987 25000SQL> SELECT ssn "social security number "FROM employeeSocial security number88866555533344555512345678998765432199988777766688444453453453987987987All the values in the attribute ssn will be selected and re named as social security numberSelection of records conditionally4.5.2 SELECT .. FROM …. WHERESELECT ssn, salaryFROM EmployeeWHERE dno = 1 ;This statement select the attributes ssn and salary of the employee who is in department number =1ssn salary888665555 55000Selection of records with complex conditions more than oneComparison OperatorsThe operators include :SELECT ssn, salaryFROM EmployeeWHERE salary > 25000;ssn salary888665555 55000333445555 40000123456789 30000987654321 4300066688444 38000Logical AND, OR and NOTSELECT ssn, salaryFROM EmployeeWHERE dno = 4AND salary > 25000;Ssn Salary987654321 43000SELECT ssn, sex, salary, dnoFROM EmployeeWHERE dno = 4OR dno= 1sex salary Dno ssn8886655m 55000 1559876543f 43000 421f 25000 4999887777m 25000 4987987987There are two ways of expressing negation :SELECT ssn, sex, salary, dnoFROM EmployeeWHERE SEX !=’m’;sex salary Dnossnf 43000 49876543219998877f 25000 477SELECT ssn, sex, salary, dnoFROM EmployeeWHERE not ( SEX =’m’);4.5.3 ORDER BYThe ORDER BY clause sorts the rows in the result table in a specified order.SQL> SELECT ssn, salaryFROM EmployeeORDER BY salary ASC;Only two columns are selectedSsn Salary999887777 25000453453453 25000987987987 25000123456789 3000066688444 38000333445555 40000987654321 43000888665555 55000By default the order is in ascending order.It is also possible to specify a descendng order :SQL> SELECT ssn, salaryFROM EmployeeORDER BY salary desc ;:Ssn Salary888665555 55000987654321 43000333445555 4000066688444 38000123456789 30000987987987 25000453453453 25000999887777 25000Sorting a result table on a combination of columns is also possible : SQL> SELECT ssn, salary,dnoFROM employeeORDER by salary , dnoThe records will be sorted in ascending of the salary first and thenby ascending order of dnoSsn Salary dno999887777 25000 4987987987 25000 4453453453 25000 5123456789 30000 566688444 38000 5333445555 40000 5987654321 43000 4888665555 55000 14 .5.4 Character / Pattern MatchingThose fields which are defined as char / varchar can be selected using a variety of strings and pattern matching operators. Note that the string has to be enclosed in single quotes :LIKE commands:SQL> SELECT fname, minit, lnameFROM employeeWHERE fname LIKE 'J%';Selects all the fnames that begin with JFname miLnamenitJames E BorgJohn B SmithJennifer S WallaceJoyce A EnglishSQL> SELECT fname , minit, lnameFROM employeeWHERE fname like '%k%';Selects those fnames which have the letter k in the fnamelname DnoFname MinitFranklin T 'Wong' 5SQL> SELECT fname , minit, lnameFROM employeeWHERE fname like '%d';Selects all the fnames that end dFname minit lnameAhmad’v Jabbar4.5.5 IN and BETWEENUse of IN …SQL> SELECT fname, minit, lnameFROM employeeWHERE minit IN(‘ j ’, ‘k’, ‘s’ );Use of BETWEENSQL> SELECT ssn, salaryFROM employeeWHERE salary BETWEEN 30000 and 40000; Ssn Salary333445555 40000123456789 3000066688444 38000NULL values and non NULL values.A NULL value corresponds to 'value not known' or 'value not applicable’ and blank. Null does not mean zero..SQL> SELECT salaryFROM employeeWHERE slary = null4.6. Built-in FunctionsThe following are some of the built-in functions that Oracle SQL*Plus has to offer. SUM - sum total of a numerical column.COUNT - count of the total number of rows satisfied by some querycondition.MIN - returns the minimum value among a list of valuesMAX -returns the maximum value among a list of valuesAVG - returns the average value of a list of valuesABS - returns the absolute value.SQL> SELECT sum(salary)FROM EmployeeSQL> SELECT min (salary)FROM Employee;SQL> SELECT max(salary)FROM Employee;SQL> SELECT avg(salary)FROM Employee;SQL> SELECT count (dno)FROM Employee;SQL> s elect count(distinct dno) from team;COUNT function simply counts individual rows on a ROW BY ROW basis, across the whole table. This time the COUNT function is executed on a GROUP BY GROUP4.7 GROUP BYThe GROUP BY clause is group the records based on a particular attribute values SQL> SELECT dnoFROM employeeGROUP BY dnodno145The more useful application of group will beSQL> SELECT dno, count (*)FROM employeeGROUP BY dno;dno count1 14 35 4Any attribute which is listed in the GROUP BY clause must appear in the target list of the select statement.SQL> SELECT dno, count (*), sum(salary)FROM employeeGROUP BY dno;dno Count expression1 1 550004 3 930005 4 1330004.8. HAVING clauseThe HAVING clause may only be used with an associated GROUP BY clause. However a GROUP BY clause need not have a HAVING clause.SQL> SELECT dno, count (*), sum(salary)FROM employeeGROUP BY dno;HAVING count (*) > 2Dno Count expression4 3 930005 4 133000SQL> select dno , avg(salary)From employeegroup by dnohaving avg(salary) >30000;SQL> SELECT dno, sum(salary)FROM EmployeeGROUP BY dnoHAVING dno >1;4.9 Calculations with select Statements+,-,/ and * may be used with numeric values (and attributes), as well and in addition to numeric functions, to calculate new results.SQL> SELECT ssn, (1.2 * salary) AS new_sal, dnoFROM employeeWHERE dno=4;ssn New_Salary Dno9876543516000 42130000 499988777730000 4987987987SQL> SELECT (max(salary) - min(salary)) as difference_salaryFROM employeeWHERE dno =4 ;Difference_salary216004.10 Removing Duplicate RowsAt times the result of a retieval will produce identical rows;SQL> SELECT dnoFROM employeeDuplicate rows may be elimenated by specifying the distinct operator;SQL> SELECT distinct dnoFROM employee4.11 SQL – Reserved WordA partial list of SQL (Oracle) keywords generally used :ALTER DROP PACKAGE SEQUENCEANALYZE EXPLAIN PLAN SETAND FROM PROCEDURE SNAPSHOTAUDIT FUNCTION PROFILE SYNONYMBODY GRANT RENAME TABLECLUSTER INDEX RESOURCE TABLESPACECOMMENT INSERT REVOKE TRANSACTIONCOMMIT LINK ROLE TRIGGERCONTROLFILE LOCK ROLLBACK TRUNCATECOST LOG SAVEPOINT UPDATECREATE NOAUDIT SCHEMA USERDATABASE NOT SEGMENT VIEWDELETE OR SELECT WHERESQL*LoaderThis is an Oracle Utility used for loading data from non-Oracle data sources eg external files.Try IMPORT & EXPORT utilities.4.11 Rollback, CommitThe insert, update and delete statements are called DML (Data Manipulation Language) statements and can be reversed - to a point.Your DML statements are initially held in a local buffer space - they are not immediately transferred to the central Oracle8 database and held permanently.To transfer your DML statements to the Oracle8 database and make these changes permanent, use :SQL> commit;This will explicitly save your changes to the database. Once done, this cannot be undone.To undo the commandsSQL> rollback;Following statements will automatically cause a commit to happen∙quit∙exit∙create table∙create view∙drop table∙drop view∙grant∙revoke∙connect∙disconnectCASE STUDY:System Specification: A database has been designed for thefollowing specification and the relational schema is givenA University Accommodation Office maintain Students Accommodationinformation in the following database.Student may rent a room in a university owned hall of residence or student Flat. When a student joins the university he/she is assigned to a member of Staff who acts as his/her advisor.Each hall provides only single room. The flat provides single roomaccommodation for a group of three , four or five studentsA student may rent a room in a hall of residence or in a student flat. Leaseagreements are signed at the start of the academic year for a rental period of one semester or one year. There are two semesters in year.Each flat is inspected by a staff on a regular basis to ensure that theaccommodation is well maintained.The Relational Schema1.Student (Stu_ID, Stu_name, Stu_address, sex, date_birth, category,Nationality, smoker, special_needs, comments, status, course, room_No., room_type)2.Advisor (Staff_ID, adv_name, position, dept_name, phone_No., room_No.)3.Hall (hall_No., address, tele_No., staff_ID, room_No., place_No., rent_rate)4.Flat (flat_No., address, number_bed, room_No., staff_ID, place_No,rent_rate)5.Lease agreement: (lease_No., duration, Stu_name, Stu_id, place_No,room_No., address, enter_date, Leave_date)6.Inspection (insp_No., date, condition, comments, room_No, staff_name)7.Staff (Staff_ID, Staff_name, address, date_birth, sex, position, location)Tasks:An initial investigation suggests the following tables are required ....1.Create the above tables. You may wish to add extra attributes but theseneed to be justified.For every table it is up to you to decide the appropriate data type for each one of the columns based on the column descriptions.2.Insert 10-15 rows of data in each table. The insert statements must bestored in a script file.You must ensure that your data supports the queries below。