Populating and Querying tables

1. Populating and Querying tables Insert and mostly View (DML)

2. Contents of this lecture • Inserting data into a single table. • Queries • On two tables. • On two joined tables. • On more than two joined tables. • Lop-sided queries (outer joins). • Tables joined to themselves. • Group data using the GROUP BY clause • Include or exclude grouped rows by using the HAVING clause

3. Inserting data into a table • To add a row of data to a table INSERT INTO <table> VALUES (value1, value2, …valueN) • If the value list matches the column list exactly, there is no need to list the column names. • If the value list does not match the column list exactly, there is a need to list the column names.

4. Example • insert into Expert values (2222221,'Dr. O''Meara','Orthopaedic'); • Where the expert table was set up as:- CREATE TABLE Expert ( Expert_Id numeric(7,0), Expert_Name varchar(50), Expertise_area varchar(15), PRIMARY KEY (Expert_Id)) Note also, if you want to put an apostrophe in a string, put in two apostrophes.

5. Referential integrity • The above tables have no foreign keys and a straightforward insert does not compromise referential integrity. • If a table is constrained by foreign key values, then there is a need to see what is in the other table. • Retrieving data from a database • Data can be retrieved from a table or set of tables using the SELECT statement

6. Common errors on insert • Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__Contract__05D8E0BE'. Cannot insert duplicate key in object 'Contract'. • This happens when you put a value in the primary key field that already appears in the file – i.e. record already exists. • INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__Contract__Custom__06CD04F7'. The conflict occurred in database 'pobyrne', table 'customer', column 'Customer_number'. • In this statement, I tried to add a contract to the contract table, referencing a customer that was not in the customer table. The contract table has customer_number as a foreign key referencing the customer table.

7. Enquiry Access Paths • Traversing a data model • Start with a data model or ERD.

8. ERD

9. Which customers have bought stock supplied by ‘supplierId’?

10. What are the sales that this staff member made?

12. Multi-table queries

13. Revisiting database structure External Schema Conceptual Schema Internal Schema Physical Schema

14. Level visible to user Multiple views of the system e.g. View an order - see limited product and customer information Only the database Administrator may access the whole database at this level Each external view is defined by means of an external schema Provides definitions of each external view. Written in a Data Definition Language. individual to the user or user group. accessed through a 3GL, a query language or a special purpose forms or menu-based language The external schema

15. Queries • User creates a query, to provide a specific view of the data • Database application converts this into a statement in SQL • DBMS executes the SQL statement • BASE TABLE - a table storing data • DERIVED TABLE - virtual result table from a query

16. Query essentials • Selection • This is where rows from a table are extracted on the basis of the value of one or more of the fields • Projection • This is where columns from a table are extracted on the basis of the value of one or more of the fields • Join • This is where rows and columns from more than one table are extracted

17. Selection • Selects only those ROWS which satisfy the selection criteria • Example: • Set up a query to display claims where the state is trivial (T): Select * from Claim where Claim_state = 'T'

18. Projection • A projection of a table onto a subset of its attributes (i.e. throw away unnecessary attributes) • e.g. project Expert Name and Expertise from Expert: • Select Expert_Name, Expertise_area from Expert

19. Joins • Tables can be joined together on fields which have the same attributes: SELECT * FROM products, suppliers WHERE products.supplierId = suppliers.supplierId; • Note: Tables can be joined in this way even if they were set up without a foreign key.

20. More usually, the query uses ‘join’ • Select * from products join suppliers on products.supplierId = suppliers.supplierId

21. 3-table join • If you have a query that involves more than one relationship, you must join over relationships • For example (Northwind), • “Show me the sales for each product, ordered by category and product , giving the categoryname and product name.

23. NorthWind ERD

24. Enquiry Access Path

25. Joins required • Categories needs to join with products • Products needs to join with Order details • Joins can be nested A join B on A.x = B.x join C on B.y = C.y Join D on C.z = D.z etc.

26. In this example, there are 2 joins • Categories join products on Categories.categoryId = Products.CategoryId • Products join [order details] on products.productId = [order details].productId • There is also a derived column, from the [order details] table. Income = (unitPrice * quantity) - discount

27. Query use Northwind; Select categoryName, ProductName, (([order details].unitPrice*Quantity)-Discount) as income from categories join products on categories.categoryid = products.categoryid join [order details] on products.productid = [order details].productid;

28. Result categoryName ProductName income --------------- ---------------------------------------- ------------------------ Beverages Chai 647.79999 Beverages Chai 259.20001 Beverages Chai 288.0 Beverages Chai 215.85001 Beverages Chai 172.8 Beverages Chai 215.85001 Beverages Chai 144.0 Beverages Chai 345.60001 Beverages Chai 216.0 Beverages Chai 719.79999 Beverages Chai 143.85001 Beverages Chai 180.0 Beverages Chai 360.0 Beverages Chai 54.0

29. Improve layout of output Select categoryName, cast(ProductName as char(12))as Product, cast(([order details].unitPrice*Quantity)-Discount as decimal(10,2)) as income from categories join products on categories.categoryid = products.categoryid join [order details] on products.productid = [order details].productid

30. Improved output categoryName Product income --------------- ------------ ------------ Beverages Chai 647.80 Beverages Chai 259.20 Beverages Chai 288.00 Beverages Chai 215.85 Beverages Chai 172.80 Beverages Chai 215.85 Beverages Chai 144.00 Beverages Chai 345.60 Beverages Chai 216.00 Beverages Chai 719.80