Database Design and Management Rolational Model and SOL The result will be “Deptid D103 EKG schema Change Statements -2.nbe- chery) addy ee p othe Schema can be changed b: fl The DROP Command * The DROP command is used to remove the object (table, domains and raints) from the database, There are two options for the DROP. command - CAS¢ E and Orestricr. eg ae Peleme nts ye = PRE dee Ae Tse the RESTRICT option, the wen AY individual the ly drop each element in the schema is dropped only if ‘OP command t and can not be executed, acted s some database schema CA‘ ‘ADE option is Student_database 7 DROP SCHEMA Stic ident_database canna x bie y «table is to be deleted then the SQL. command would be. Seis to be del ~~ DROP TABLE Student CASCADE; —7 TSb\e A ede The DROP TABLE command not only deletes all Otherwise to Temove completel chosen. For example —to remove the the records in the table if Successful, but also removes the table definition from the catalog. If it is desired to delete only the records but to leave the table definition for future use, then the DELETE command. For example - The following SQL statement deletes all rows in the "Students" table, without deleting the table : ELETE FROM Students; ERER the ALTER Command The ve awicoli ere are SQL commands for alteration of table. That means we can add new co mi ae is se i ands. or delete some column from the table using these alteration comma o TECHNICAL PUBLICATIONS an up-thrust for knowledgoDatahace Design and Management Petters gy, ny Syntax for Adding columns ALTER TABLE table_hame ADD colamn_naime datatype: Example “AadhaNo FirstName MiddleName —_ LastName Address Shivaji nagar -Chandani chowk a4 mn KKK LLL Vian nagar = Mumb; If we execute following command 5 x TER TABLE Customers z | ADD Email ‘varchar(30); | Then the result will be as follows - | _AadharNo FirstName MiddleName LastName City Ema AAA BBB ccc Pune Nu ppp EEE FFF Pune uy GGG HHH ML Delhi NULL w _ KKK LLL | Mumbai NULL Syntax for Deleting columns ALTER TABLE table name” DROP COLUMN column_name; Example Consider following table - [Tadanarne FirstName iddleName LastName City 1 AAA BBB ccc Pune 222 Dpp EEE FEF Shivaji nagar _ Los Tin rae : 333 GcG HHH Ml Chandanichowk Pe" Wy Peake, LLL a> Mumbs! o TECHNICAL PUBLICATIONS. - an up-thrust for knowledgeDatabase Design and Management 2-99 _Retatonel Model ord SO If we execute following command AUTER TABLE Ciintomors DROP COLUMN Address; Then the result will be as follows - ~AadtharNo City] ut Pune | 22 Pune = || 333 GGG HHH Ih Delhi | 444 uw KKK LLL Mumbai | rae Review Question | 1. Describe DROP TABLE command of SQL with both the options CASCADE and RES l ERG Aggregate Functions + An aggregate function allows you to perform a calculation on a set of values to i ——_— retuma single scalar value. © SQL offers aE = DuTTein ag ‘aggregate functions : 1. Average: avg 2. Minimum : min 3. Maximum : max oO 4, Total:sum ote gered Ty 5. Count: oS sac + The aggregate functions thét accept an expression parameter can be modified by the keywords DISTINCT 6r ALL. If neither is specified, the result is the same as if ALL were specified. Modifies the expression to include only distinct values that are not NULL Includes all rows where expression is not NULL * Syntax of all the Aggregate Functions -AVG([ DISTINCT | ALL | expression) COUNT(*) COUNT( [ DISTINCT | ALL } expression ) MAX( [DISTINCT | ALL | expression) o TECHNICAL PUBLICATIONS - an up-thrust for knowledgead Management yssion) iN | DISTINCT | ALL | expre: peal pIsTINcT | ALL J expression) jg used to compute average Drotadase Desir?’ value. For example — 1, The avg function ths of the students we can USC ‘SOL Statement SELECT AVG(marks) | FROM Students ; e values in the specs; db, s The Count function is used to count the total number of re Specify , j he works on both numeric and non-numeric data type. COUNT (*) js wr implementation of the COUNT function that returns the count of all the ;,, specified table, COUNT (*) also considers Nulls and duplicates, Foy average mal an Consider following table Test oo Test§ id value BAe f 100 __ SQL Statement SELECT COUNTI}=> = § . 7 FROM Test 2 Soe Output ea 4 LECT COUNT(ALL id). FROM Test Output 3 is * The min function j : a metion is used to get the minimum val ‘ ve created Test table pecified column, |aaa patanase Design and Management 2-33 ee SELECT Max(value) FROM Test Output 400 Rotational Model and SOL. + The sum fimetion is used to get total sum value from the example - Consider the above created Test table ‘SOL Statement © SELECT sum(value) ey FROM Test 7 1000 specified column. For Consider, the following database, Student(RollNo, Name, Address) Subject(Sub_code, Sub_Name) Marks (Roll_no, Sub_code, Marks) Write following queries in SQL. Find average marks of each student, along with the name of Student ee Solution : SELECT Name, AVG(Marks) FROM Student.Marks WHERE Student Roll No=Marks.Roll_No Built-in Functions + In SQL a built-in function is a piece for programming that takeg zero or more inputs ae See eet and returns. value. = PRS pag + Anexample of a built-in functions is ABS(), which when given a value calculates the absolute (non-negative) value of the number. Output Result © TECHNICAL PUBLICATIONS. - an up-thrust for knowledgeSOlational ny, Database Design and Management Mathematical Functions ; Value Returned Funetion ee — ne ees ABS(m) Absolute value of m POWER (m1) m raised to the nth power Dd m rounded to the nth decimal place P ROUND (m[.n ee la a RCA, m truncated to the nth decimal place TRUNC (m[,]) - SIN(n) sine (n) p cosine (n) tan (n) j : positive square root of n e raised to the power n logarithm of n1, base n2 smallest integer greater than o1 ey s greatest integer smaller than or equal ton. -lifn<0,0ifn=0,and1 Value Returned First letter of each word is changed to uppercase and all other letters are in lower case, __Allletters are changed to lowercase. o TECHNICAL PUBLICATIONS. - an up-thrust for knowledgeDatabase Design and Management 2-35 Relational Model and SQL UPPER (s) Allletters are changed to uppercase. CONCAT (s1, s2) Concatenation of s1 and s2. Equivalent to s1 || s2 LTRIM (s[, set} ) Returns s with characters removed up to the first character not in set; defaults to space TRIM (s[ set ]) Retums s with final characters removed after the cums o with imal characters! last character not in set; defaults to space | REPLACE (s, searchs[, Returns s with every occurrence of search_s ins replace_s }) replaced by replace_s; default removes search_s | SUBSTR (s,m {,n]) Retums a substring from s, beginning in position mand n characters long; default returns to end of s. Returns the number of characters in s. For ex SELECT C Date and Time Functions _ Function Value Returned 3A .DD_MONTHS ( dyn) Date d plus n months | LAST_DAY (d) Date of the last day of the month containing d = Number of months by which e precedes d | NEW_TIME ( d, a, b) ‘The date and time in time zone b when date d is for time zone a /NEXT_DAY (4, day) bea e Date of the first day of the week after d Current date and time For example SELECT SYSDATE(); © TECHNICAL PUBLICATIONS. an up-thrust for knowledgeDatabase Design and Management 2-36 Relational Mode, a nd Will result in 2019-06-27 10:02:39 ERE set Operations Set is a collection of elements on which union, intersection and difference ope, “ation of elemer can be performed. 1) Union : To use this UNION clause, each SELECT statement must have QA i) The same number of columns selected same number of columns selected ou ii) The same number of column expressions iii) The same data type and oe OF fata type free us doe ig, iv) Have them in the same order This clause is used to combine two tables using untOR opeator. I replaces the operator in the query. The union operator eliminates duplicate while the union al] g,~ s will retain the duplicates, Syntay The basic syntax of a UNION clause is as follows - SELECT columat [, column2] FROM table1 [, table2 | [WHERE condition] UNION SELECT column1 {, column2 | FROM table! [, table2 ] j IWHERE condition) < Here, the given condition could be any given expression based on your requirement. Example : Find the names of the students who have reserved the ‘DBMS’ book or ‘OS Book The query can then be written by considering the Student, Reserve and Book table as SELECT S.sname 1 FROM Student S, Reserve R, Book B Sk WHERE S.sid=R.sid AND R.isbn=B.isbn AND B.bname="DBMS' UNION SELECT S.sname FROM Student S, Reserve R, Book B WHERE S.sid=R.sid AND Risbn=B.isbn AND B.bname='OS' o TECHNICAL PUBLICATIONS. - an up-thrust for knowledgeDatabase Design and Management Relational Model and SOL Database Design ane Relations! & 2) Intersect : The confmon entries between the two tables can be represented with the help of Intersect operator. It replaces the AND operator in the query. ——_ syntax? The basic syntax of a INTERSECT clause is as follows - SELECT columni |, column? | FROM tablet |, table2 | {WHERE condition} INTERSECT SELECT column |, column2 | FROM table! [, table2 | IWHERE condition] Example : Find the students who have reserved both the ‘DBMS’ book and ‘OS’ Book The query can then be written by considering the Student, Reserve and Book table as. SELECT S.sid, Ssname FROM Student S, Reserve R, Book B WHERE S.sid=R.sid AND R.isbn= INTERSECT > SELECT S.sname FROM Student S, Reserve R, Book B WHERE S.sid=R.sid AND Risbn=1 isbn AND B.bname="DBMS’ isbn AND Bbname="0S' ANP se bee 3) Except : The EXCEPT clause is used to represent the set the query. Pt ———— This query is used to represent the oe that are present in one table and not in Py oh other. prone oy ae xe Syntax: ifference it The basic syntax of a EXCEPT clause is as follows - "SELECT column! [, column? | FROM tablel [, table2 | [WHERE condition] EXCEPT SELECT column! [, column2 } FROM tablei |, table2 | [WHERE condition| Example : Find the students who have ae both the ‘DBMS’ book but not reserved ‘OS’ Book ‘The query can then be written by considering the Student, Reserve and Book table as SELECT §.sid) Ssname ~Z FROM Student 8, Reserve R, Book B WHERE 8.8id=R.sid AND R.isbn=B.isbn AND B.bname='DBMS' EXCEPT @ TECHNICAL PUBLICATIONS. ~ an up-thrust for knowledge™ Database Design and Management Relatioy {SRERCTS snaine E FROM Student 8, Reserve th book WHERE S.sid=R sid AND , A My, como