1 / 72

Database

Database . SQL. Relational language SQL. SQL or Structured Query Language SQL is an interactive Query language and database programming Language. SQL History. Originally (mid-’70s) defined as Sequel in System R project at IBM San Jose Research Center

jonesamy
Download Presentation

Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database SQL

  2. Relational language SQL • SQL or Structured Query Language • SQL is an interactive Query language and database programming Language

  3. SQL History • Originally (mid-’70s) defined as Sequel in System R project at IBM San Jose Research Center • Name change to SQL, multiple iterations of standards (’86, ’87, ’89, ’92,…) • Has usual good and bad aspects of much-used, much-extended languages • Think of C++ or Fortran-90…

  4. SQL • From American National Institue ANSI, it was SQL-86 (ANSI 1986) and SQL-2 (SQL-92) • SQL Language is divided into • DDL : Data Definition Language • DML : Data Manipulation Language • DCL : Data Control Language

  5. DDL • It is commands used for: • Creates databases, tables, indices • Create views • Specify integrity constraints • DDL commands are • CREATE • ALTER • DROP

  6. DML • For values in the database • Perform queries • Perform updates • DML commands: • SELECT • UPDATE • DELETE • INSERT

  7. DCL • For controlling the access of data • DCL commands are • GRANT • REVOKE

  8. SQL - DDL • It used to define and manage the structure of tables in the database • Its statements as • CREATE TABLE CREATE INDEX • ALTER TABLE • DROP TABLE DROP INDEX

  9. Creating tables create table name (attr1 domain1, attr2 domain2, …) Can also have integrity constraints • not null • unique • primary key (attr1, attr2, …) • check (predicate) • To be a primary key, must be non-null and unique • Different SQLs differ about whether not null is implied by primary key

  10. CREATE Commands • Create the table S (s#, Sname, Status, City) CREATE TABLE S (s# CHAR(5), Sname CHAR(20), Status INT, City CHAR(15), PRIMARY KEY (s#)).

  11. Data Definition Language • Every attribute has a domain • Some common ones: • char(n): character string of fixed length n • varchar(n): character string of length up to n • int: integer, exact size machine-dependent • smallint: another (usually smaller than int) integer

  12. More common domains • numeric(p,d): fixed point number with p total digits, d of them to the right of the decimal point • real, double precision: floating-point numbers • float(n): floating-point with precision at least n • date: a calendar date (day, month, year) • time: hours, minutes, seconds • datetime: a time on a date

  13. Example: Default Values CREATE TABLE Students( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT ‘123 Sesame St.’, phone CHAR(16) );

  14. Other Keys CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT,PRIMARYKEY (productID), UNIQUE (name, category)) There is at most one PRIMARY KEY;there can be many UNIQUE

  15. Foreign Key Constraints • (name, category) must be a PRIMARY KEY CREATE TABLE Purchase ( prodName CHAR(30), category VARCHAR(20), date DATETIME, FOREIGNKEY (prodName, category) REFERENCES Product(name, category)

  16. Example • Create a database which can use in Seaport • The database consists of 3 tables: • Sailors (sid integer, sname char, rating integer, age real) • Reverses (sid, bid, day) • Boats (bid integer, bname char, color char)

  17. DROP command • DROP TABLE BOATS • DROP TABLE REVERSES • DROP TABLE SAILORS

  18. ALTER COMMAND • ALTER TABLE Sailors ADD address CHAR(30) • ALTER TABLE Boats DROP colors

  19. INDEX • CREATE INDEX S ON Sailors (sid) • CREATE INDEX R ON REVERSES (sid, bid, day) • CREATE INDEX B ON Boats (bid) • DROP INDEX R

  20. Example 1 Since Status WorksIn Professor Department CREATE TABLE WorksIn ( Since DATE, -- attribute Status CHAR (10), -- attribute ProfId INTEGER, -- role (key of Professor) DeptId CHAR (4), -- role (key of Department) PRIMARY KEY (ProfId), -- since a professor works in at most one department FOREIGN KEY (ProfId) REFERENCES Professor (Id), FOREIGN KEY (DeptId) REFERENCES Department )

  21. Example 2 Date Price Sold Project Part Supplier CREATE TABLE Sold ( Price INTEGER, -- attribute Date DATE, -- attribute ProjId INTEGER, -- role SupplierId INTEGER, -- role PartNumber INTEGER, -- role PRIMARY KEY (ProjId, SupplierId, PartNumber, Date), //WHY DATE? FOREIGN KEY (ProjId) REFERENCES Project, FOREIGN KEY (SupplierId) REFERENCES Supplier (Id), FOREIGN KEY (PartNumber) REFERENCES Part (Number) )

  22. DML SELECT • The basic format for the SELECT command is SELECT <attribute list> FROM <table list> WHERE <condition>

  23. Simple SQL Query Product SELECT *FROM ProductWHERE category=‘Gadgets’ “selection”

  24. Examples • Retrieve the birthdate and address of employee whose name is “John B. Smith” • SELECT Bdate, Address FROM Employee WHERE Name = ‘John B. Smith’

  25. Example • Retrieve all Sailors name whose reverses boat id 103 • SELECT sname FROM Sailors , Reverses WHERE Sailors.sid = Reverses.sid AND Reverses.bid=103

  26. ORDER BY • The Select command has sentential ORDER BY • Select S.sname FROM Sailors S, Reverses R WHERE S.sid = R.sid AND R.bid = 103 ORDER BY age DESC

  27. The LIKE operator • s LIKE p: pattern matching on strings • p may contain two special symbols: • % = any sequence of characters • _ = any single character Product(PName, Price, Category, Manufacturer) Find all products whose name mentions ‘gizmo’: SELECT *FROM ProductsWHERE PName LIKE ‘%gizmo%’

  28. Eliminating Duplicates SELECTDISTINCT category FROM Product Compare to: SELECT category FROM Product

  29. Joins in SQL • Connect two or more tables: Product Company What is the connection between them ?

  30. Joins Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all countries that manufacture some product in the ‘Gadgets’ category. SELECT countryFROM Product, CompanyWHERE manufacturer=cname AND category=‘Gadgets’

  31. Joins Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phoneNumber, city) Find names of people living in Seattle that bought some product in the ‘Gadgets’ category, and the names of the stores they bought such product from SELECTDISTINCT persname, storeFROM Person, Purchase, ProductWHERE persname=buyer AND product = pname AND city=‘Seattle’ AND category=‘Gadgets’

  32. Complex Correlated Query Product ( pname, price, category, maker, year) • Find products (and their manufacturers) that are more expensive than all products made by the same manufacturer before 1972 Powerful, but much harder to optimize ! SELECT DISTINCT pname, maker FROM Product AS x WHERE price > ALL (SELECT price FROM Product AS y WHERE x.maker = y.maker AND y.year < 1972);

  33. Aggregate Operators • COUNT (*) • COUNT ( [DISTINCT] A) • A is a column • SUM ( [DISTINCT] A) • AVG ( [DISTINCT] A) • MAX (A) • MIN (A) • Count the number of sailors SELECT COUNT (*) FROM Sailors S

  34. Find name and age of the oldest sailor(s) SELECT S.sname, MAX (S.age) FROM Sailors S • This is illegal, but why? • Cannot combine a column with a value SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2)

  35. Examples • What is the average distance between plazas? select avg(distfromprev) from plazas • How many events are there? select count(*) from events  29 • How many distinct times are there? select count(distinct occurredat) from events  24 • How do we find the duplicates?

  36. Aggregation: Count SELECT Count(*) FROM Product WHERE year > 1995 Except COUNT, all aggregations apply to a single attribute

  37. Aggregation: Count COUNT applies to duplicates, unless otherwise stated: SELECT Count(category) same as Count(*) FROM Product WHERE year > 1995 Better: SELECT Count(DISTINCT category) FROM Product WHERE year > 1995

  38. Aggregate functions • GROUP BY & HAVING • GROUP BY for grouping the Query results • Having to provide a condition on the group

  39. Example SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON WHERE Pnumber = PNO GROUP BY Pnumber, Pname HAVING COUNT(*) >2

  40. Grouping and Aggregation Usually, we want aggregations on certain parts of the relation. Purchase(product, date, price, quantity) Example 2: find total sales after 10/1 per product. SELECT product, Sum(price*quantity) AS TotalSales FROM Purchase WHERE date > “10/1” GROUPBY product Let’s see what this means…

  41. Grouping and Aggregation 1. Compute the FROM and WHERE clauses. 2. Group by the attributes in the GROUPBY 3. Select one tuple for every group (and apply aggregation) SELECT can have (1) grouped attributes or (2) aggregates.

  42. HAVING Clause Same query, except that we consider only products that had at least 100 buyers. SELECT product, Sum(price * quantity) FROM Purchase WHERE date > “9/1” GROUPBY product HAVING Sum(quantity) > 30 HAVING clause contains conditions on aggregates.

  43. Find all authors who wrote at least 10 documents: • Attempt 1: with nested queries This isSQL bya novice SELECTDISTINCT Author.name FROM Author WHERE count(SELECT Wrote.urlFROM WroteWHERE Author.login=Wrote.login) > 10

  44. Find all authors who wrote at least 10 documents: • Attempt 2: SQL style (with GROUP BY) This isSQL byan expert SELECT Author.name FROM Author, Wrote WHERE Author.login=Wrote.login GROUP BY Author.name HAVING count(wrote.url) > 10 No need for DISTINCT: automatically from GROUP BY

  45. SQL • Using the following tables Sailors

  46. Example Boats Reverses

  47. Queries • Find the names and ages of all sailors • Find all sailors with rating above 7 • Find the name of sailors who have reserved boat number 104

  48. Quiz • Write an SQL query for following, and show the result on the example tables: • Find all course names that were not taught in 1997 • List the professor names along with all the departments ids where a professor has taught • Find the names of students who took courses in all the departments that offered a course in Fall of 1995 or 1997.

  49. Exercises Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Company (cname, stock price, country) Person(per-name, phone number, city) Ex #1: Find people who bought telephony products. Ex #2: Find names of people who bought American products Ex #3: Find names of people who bought American products and they live in Seattle. Ex #4: Find people who have both bought and sold something. Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.

More Related