Query Languages How to build relational database www
Query Languages How to build relational database www. assignmentpoint. com
SQL n SQL is a query language for relational databases. n Contains: n Data Definition Language to define databases n Data Manipulation Language to manipulate databases. n SQL is widely accepted and is used by most relational DBMSs. n Is being standardized. www. assignmentpoint. com
The importance of SQL n Since SQL is used in almost all relational databases, once you know SQL you can probably construct and manipulate databases in all RDBMs. n Knowing SQL makes you a (beginning) ORACLE, Informix, Sy. Base, Ada. Bas, and so on programmer! www. assignmentpoint. com
Functionalities of SQL n SQL provides n On-line and embedded use. n Precompilation of embedded queries. n Dynamic database definition and alteration. n Maintenance of indexes n View mechanism n Authorization mechanism n Automatic concurrency control n Logging and database recovery n Report formatting www. assignmentpoint. com
Tables in SQL recognizes n Base Tables n n real tables that physically exist in the database. There are physically stored records and possibly physically stored indexes directly corresponding to the table Views n virtual tables that do not physically exist but look to the user as if they do www. assignmentpoint. com
Data Definition n An SQL database consist of n Database Spaces n Base tables n Indexes n Views www. assignmentpoint. com
Database Spaces n DBSpace is a section of physical disk. n It consists of n Base tables n Indices n Views n All can be dynamically dropped from DBSpaces. n DBSpaces allow the DB administrator to distribute data accesses over different disks. www. assignmentpoint. com
Indexes n As we know, indexes can improve search performance. n Cost: more space needed and slower insertion. n Indexes can be defined over any combination of attributes in a base table. n Automatically maintained in SQL. n Users never directly use an index. www. assignmentpoint. com
Views n Correspond to external schemas. n Derived from one or more base tables or views. n Computed dynamically. www. assignmentpoint. com
Operations in SQL n For tables: n CREATE, ALTER, DROP n For indexes n CREATE, DROP n For views: n CREATE, DROP www. assignmentpoint. com
![Creating tables CREATE <table name> (<coldecl> [, <coldecl>*], [, <pkdef> [, <fkdef>*]); <coldecl> :](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-11.jpg "Creating tables CREATE <table name> (<coldecl> [, <coldecl>*], [, <pkdef> [, <fkdef>*]); <coldecl> :")
Creating tables CREATE <table name> (<coldecl> [, <coldecl>*], [, <pkdef> [, <fkdef>*]); <coldecl> : = <col><type>[NOT NULL] <type> : = integer|smallint|float(p)| decimal(p, q)|char(n)| varchar(n)|long varchar| date|time www. assignmentpoint. com
![Creating tables continued <pkdef> : = PRIMARY KEY (<colname> [, <colname>*] <fkdef> : =](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-12.jpg "Creating tables continued <pkdef> : = PRIMARY KEY (<colname> [, <colname>*] <fkdef> : =")
Creating tables continued <pkdef> : = PRIMARY KEY (<colname> [, <colname>*] <fkdef> : = FOREIGN KEY (<colname>[, <colname>*]) REFERENCES <table> [ ON DELETE <effect>] www. assignmentpoint. com
More on creating tables <effect> : = RESTRICT | CASCADE | SET NULL n What happens when the tuple in the referenced table with that value is deleted RESTRICT: Do not delete as long as there tuples in other table with that foreign key value n CASCADE: Delete all tuples with that foreign key value n SET NULL: Set value of foreign key to NULL. (Note violates referential integrity). n www. assignmentpoint. com
Example 1 CREATE TABLE Student (sid CHAR(5) NOT NULL, sname VARCHAR(20), address VARCHAR(70), PRIMARY KEY (sid)); OR CREATE TABLE Student (sid CHAR(5) PRIMARY KEY, sname VARCHAR(20), address VARCHAR(70)); www. assignmentpoint. com
Example 2 CREATE TABLE Enrol (sid CHAR(5) NOT NULL, cid CHAR(5) NOT NULL, grade INT, PRIMARY KEY(sid, cid), FOREIGN KEY (sid) REFERENCES Student ON DELETE CASCADE FOREIGN KEY (cid) REFERENCES Course www. assignmentpoint. com ON DELETE RESTRICT);
Altering tables I ALTER TABLE <table name> ADD {<coldecl>| <pkdef>| <fkdef>}; ALTER TABLE Enrol ADD edate DATE; n adds a new column to the table grade. For existing tuples, the value is set to NULL. www. assignmentpoint. com
Altering tables II ALTER TABLE <table name> DROP {PRIMARY KEY| <fkname>}; n Note that care must be taken when dropping columns. www. assignmentpoint. com
Dropping tables n Tables can be dropped at any time. n Dropping a table deletes both the definition and data. n Also, all views, indexes and foreign key definitions referring to this table are dropped. DROP TABLE <table name>; www. assignmentpoint. com
![Creating indexes CREATE [UNIQUE] INDEX <index> ON <table> (<colname> [<order>] [, <colname> [<order>]*]); <order>:](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-19.jpg "Creating indexes CREATE [UNIQUE] INDEX <index> ON <table> (<colname> [<order>] [, <colname> [<order>]*]); <order>:")
Creating indexes CREATE [UNIQUE] INDEX <index> ON <table> (<colname> [<order>] [, <colname> [<order>]*]); <order>: = ASC | DESC n Creates an index on named columns. With UNIQUE, no two tuples can have the same values for the indexes columns. n Example: CREATE INDEX Course ON Enrol (cid); www. assignmentpoint. com
Data manipulation n Having created the tables, indexes and views, we now need to populate the database and retrieve information from it. n In other words, we want to manipulate the data. www. assignmentpoint. com
![Retrieval SELECT [DISTINCT] <items> FROM <table> [, <table>*] [WHERE <pred>] [GROUP BY <attrs> [HAVING](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-21.jpg "Retrieval SELECT [DISTINCT] <items> FROM <table> [, <table>*] [WHERE <pred>] [GROUP BY <attrs> [HAVING")
Retrieval SELECT [DISTINCT] <items> FROM <table> [, <table>*] [WHERE <pred>] [GROUP BY <attrs> [HAVING <pred>]] [ORDER BY <attrs> ]; n Corresponds to a JOIN-SELECT-PROJECT expression in relational algebra. www. assignmentpoint. com
Predicates n The predicate <pred> is a condition formed by parentheses and boolean operators AND, OR and NOT. n A condition has the form n <attr><op>{<value>|<attr>} n and an operator is one of n < | =< | >= | != www. assignmentpoint. com
WHERE clauses n In general, WHERE clauses are constructed as in relational algebra, but with some additions n LIKE string n May contain wildcard characters %, which matches any string, and _, which matches a single character. n IN (set of values) n Tests for set membership n BETWEEN c 1 AND c 2 www. assignmentpoint. com
Example n Find Student IDs and grades for those students who read CS 51 T SELECT sid, grade FROM Enrol WHERE cid = ‘CS 51 T’; n Compare p sid, grade (s cid = ‘CS 51 T’(Enrol)) www. assignmentpoint. com
Example continued n We can embellish the way in which the result appears by including format strings in the SELECT n Example SELECT Student as sid, grade FROM Enrol WHERE cid = ‘CS 51 T’; www. assignmentpoint. com
DISTINCT n DISTINCT is used to make sure that we do not get any duplicate values. n Example SELECT DISTINCT cid FROM Enrol WHERE grade > 70; n First, find the various course numbers that qualify and then remove duplicates. www. assignmentpoint. com
More examples n The use of * in the SELECT returns all attributes SELECT * FROM Enrol WHERE cid = ‘CS 51 T’; n Find all students who obtained 60 or more for CS 51 T SELECT sid FROM Enrol WHERE cid = ‘CS 51 T’ AND grade >= 60; www. assignmentpoint. com
Yet more examples n Find all results for either or CS 51 T or CS 51 S SELECT * FROM Enrol WHERE cid IN (‘CS 51 S’, ‘CS 51 T’); n Find results for CS courses SELECT * FROM Enrol WHERE cid LIKE ‘CS%’; www. assignmentpoint. com
Ordering results n Get all results for CS 51 S and CS 51 T but order them by result SELECT sid, cid, grade FROM Enrol WHERE cid IN (‘CS 51 S’, ‘CS 51 T’) ORDER BY grade DESC; www. assignmentpoint. com
Subqueries n Notice that the result of a SELECT clause is a table which can be used in another WHERE clause. n Find course titles of the courses for which 123 was registered SELECT title FROM Course WHERE cid IN (SELECT cid FROM Enrol WHERE sid = ‘ 123’); www. assignmentpoint. com
Table labels n Sometimes we need to interrogate the same table twice. n We use table labels n Example: Get IDs from those students who did both CS 51 S and CS 51 T SELECT DISTINCT sid FROM Enrol as E 1, Enrol as E 2 WHERE E 1. Sid = E 2. Sid AND E 1. Cid = ‘CS 51 S’ AND E 2. Cid = ‘CS 51 T’; www. assignmentpoint. com
Table labels can usually be avoided n We could formulate the same query as SELECT sid FROM Enrol WHERE cid = ‘CS 51 S’ AND sid IN (SELECT sid FROM Enrol WHERE cid = ‘CS 51 T’); www. assignmentpoint. com
Use of ALL in WHERE clauses n Queries that look at all tuples satisfying a particular predicate. n Get the IDs of the students all of whose results are over 70. SELECT sid FROM Enrol as E 1 WHERE 70 < ALL (SELECT grade FROM Enrol as E 2 WHERE E 1. sid = E 2. sid); n Forms of ALL: < ALL, <= ALL, >= ALL, > ALL www. assignmentpoint. com
Union n Union allows one to union tuples from different tables. n Get Student IDs for all students whose name starts with a ‘J’ or who obtained an A for CS 51 T. SELECT sid FROM Student WHERE sname LIKE ‘J%’ UNION SELECT sid FROM Enrol WHERE cid = ‘CS 51 T’ AND grade > 70; www. assignmentpoint. com
Intersect n Allows one to intersect n Get all IDs for students whose name begins with a ‘J’ and who obtained an A for CS 51 S SELECT sid FROM Student WHERE sname LIKE ‘J%’ UNION SELECT sid FROM Enrol WHERE cid = ‘CS 51 S’ AND grade > 70; www. assignmentpoint. com
EXISTS and NOT EXISTS n Counterpart of ALL n Find name of students who have not obtained an A for any course SELECT sname FROM Student WHERE NOT EXISTS (SELECT * FROM Enrol WHERE sid = Student. sid AND grade > 70); www. assignmentpoint. com
Analysis of data n In order to help do some primitive analysis of data, SQL has some built-in functions COUNT(*) n COUNT(DISTINCT <attr>) n SUM([DISTINCT]<item>) n n where <item> may be an abstraction and does not need to be a single attribute. AVG([DISTINCT]<item>) n MAX(<item>) n MIN(<item>) n www. assignmentpoint. com
Some simple examples of data analysis in SQL n How many students are registered for at least one course SELECT COUNT(DISTINCT sid) FROM Enrol; n Find the average grade for CS 51 S SELECT AVG(grade) FROM Enrol WHERE cid = ‘CS 51 S’; www. assignmentpoint. com
Another example n How many students were above the average for CS 51 T? SELECT COUNT(*) FROM Enrol WHERE grade > (SELECT AVG(grade) FROM Enrol WHERE cid = ‘CS 51 T’); www. assignmentpoint. com
Yet another example n What is the name of the student who got the best mark for CS 51 T? SELECT sname FROM Student WHERE sid IN (SELECT sid FROM Enrol WHERE grade = (SELECT MAX(grade) FROM Enrol WHERE cid = www. assignmentpoint. com ‘CS 51 T’));
GROUP BY n A relation can be partitioned into groups according to some value. Analysis can then be done on these groups. n What are the averages for the various courses? SELECT cid, AVG(grade) FROM Enrol GROUP BY cid; www. assignmentpoint. com
HAVING n After partitioning, we can disqualify groups. n What is average results for courses with enrollment of more than 10? SELECT cid, AVG(grade) FROM Enrol GROUP BY cid HAVING COUNT(*) > 10; n COUNT is applied to each group separately. www. assignmentpoint. com
![Insertion INSERT INTO {<table>|<view>} [(<attr>] [, <attr>*])] {VALUES (<items>| <select statement>)}; n Example INSERT](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-43.jpg "Insertion INSERT INTO {<table>|<view>} [(<attr>] [, <attr>*])] {VALUES (<items>| <select statement>)}; n Example INSERT")
Insertion INSERT INTO {<table>|<view>} [(<attr>] [, <attr>*])] {VALUES (<items>| <select statement>)}; n Example INSERT INTO Enrol (cid, sid, grade) VALUES (‘CS 51 T’, ‘ 123’, 67); www. assignmentpoint. com
Insertion through a SELECT statement n For each course, get the average and insert into a RES table INSERT INTO RES (cid, average) SELECT cid, AVG(grade) FROM Enrol GROUP BY cid; www. assignmentpoint. com
![Deletion DELETE FROM <table> [<WHERE clause>]; n Example DELETE FROM Enrol WHERE cid =](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-45.jpg "Deletion DELETE FROM <table> [<WHERE clause>]; n Example DELETE FROM Enrol WHERE cid =")
Deletion DELETE FROM <table> [<WHERE clause>]; n Example DELETE FROM Enrol WHERE cid = ‘CS 51 T’; n Difference between DELETE and DROP DELETE FROM Enrol; n DELETE empties the table but leaves the table and indexes. www. assignmentpoint. com
![Updating tables UPDATE <table> SET <attr> = <expr> [, <attr> = <expr>*] [<WHERE CLAUSE>];](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-46.jpg "Updating tables UPDATE <table> SET <attr> = <expr> [, <attr> = <expr>*] [<WHERE CLAUSE>];")
Updating tables UPDATE <table> SET <attr> = <expr> [, <attr> = <expr>*] [<WHERE CLAUSE>]; n Example: Give everybody 10 extra marks for CS 35 A UPDATE Enrol SET grade = grade + 10 www. assignmentpoint. com WHERE cid = ‘CS 51 T’;
Views n Views are derived tables whose definition is stored and whose content is computed. n Can be used as base table for retrieval and view definition. n Exact condition for updating an open problem. n Currently only update iff derived form single base table n and, has rows and attributes corresponding to a unique and distinct row in base table. n www. assignmentpoint. com
Advantages of views n Views are SQL’s external schemas. They are useful Users are immune to database growth n Users are immune to database restructuring (logical data independence) n Simplified user perception n Different views of same data for different users n Automatic security for hidden data. n www. assignmentpoint. com
![Creation and deletion of views CREATE VIEW <view> [(<colname>[, <colname>*])] AS select-statement; n Example](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-49.jpg "Creation and deletion of views CREATE VIEW <view> [(<colname>[, <colname>*])] AS select-statement; n Example")
Creation and deletion of views CREATE VIEW <view> [(<colname>[, <colname>*])] AS select-statement; n Example CREATE VIEW Course. Avg (Cid, Average) AS SELECT cid, Avg(grade) FROM Enrol GROUP BY cid; n Deletion DELETE VIEW <view>; DELETE VIEW Course. Avg; www. assignmentpoint. com
The view update problem n The view Course. Avg as defined above cannot be updated, as any updates cannot be translated into the base table. n The DB administrator should decide whether a view is updatable. www. assignmentpoint. com
Authorization Mechanism n The authorization mechanism allows one to give other users permission to access and update data in a view or table. n The owner must explicitly grant necessary privileges to others, as by default the owner has all privileges and others have none. www. assignmentpoint. com
![GRANT and REVOKE GRANT <privilege> ON <table> | <view> TO <user> [, <user>*] |](http://slidetodoc.com/presentation_image_h2/9808c85a25505fd81f4bcece28073a87/image-52.jpg "GRANT and REVOKE GRANT <privilege> ON <table> | <view> TO <user> [, <user>*] |")
GRANT and REVOKE GRANT <privilege> ON <table> | <view> TO <user> [, <user>*] | PUBLIC [WITH GRANT OPTION]; REVOKE <privilege> ON <table> | <view> FROM <user> [, <user>*] | PUBLIC; www. assignmentpoint. com
Grantable privileges n These privileges are allowed: n SELECT n INSERT n UPDATE n DELETE n ALTER n INDEX n n permission to create or drop indexes on a table. ALL www. assignmentpoint. com
Use of SQL n Most users, other than the database manager, will not directly interact with SQL. n Typically, one sets up some graphical interfaces (forms) for user interaction. However, underlying the forms are SQL queries. www. assignmentpoint. com
- Slides: 54