SQL: Understanding Data Manipulation with Structured Query Language (SQL), Slides of Database Management Systems (DBMS)

Download SQL: Understanding Data Manipulation with Structured Query Language (SQL) and more Slides Database Management Systems (DBMS) in PDF only on Docsity! Chapter 7: SQL, the Structured Query Language Docsity.com Overview Introduction DDL Commands DML Commands SQL Statements, Operators, Clauses Aggregate Functions Docsity.com SQL: DDL Commands CREATE TABLE: used to create a table. ALTER TABLE: modifies a table after it was created. DROP TABLE: removes a table from a database. Docsity.com SQL: CREATE TABLE Statement Things to consider before you create your table are: The type of data the table name what column(s) will make up the primary key the names of the columns CREATE TABLE statement syntax: CREATE TABLE <table name> ( field1 datatype ( NOT NULL ), field2 datatype ( NOT NULL ) ); Docsity.com SQL: Attributes Types Numeric types _| integer integer, int, smallint, long floating float, real, double point precision formatted | decimal (i,j). dec(i,j) Character-string | fixed char (n), character (n) types length varying varchar (n): char length varying (n); character varying (n) Bit-string types | fixed bit (n) length varying bit varying (n) length Date and time date. time, datetime, types timestamp, time with time zone. interval Large types character | long varchar (n);clob, text binary blob Table 7.6 pg.164 Docsity.com Example: CREATE TABLE FoodCart ( date varchar(10), food varchar(20), profit float ); ALTER TABLE FoodCart ( ADD sold int ); ALTER TABLE FoodCart( DROP COLUMN profit ); DROP TABLE FoodCart; profit food date sold profit food date sold food date FoodCart FoodCart FoodCart Docsity.com SQL: DML Commands INSERT: adds new rows to a table. UPDATE: modifies one or more attributes. DELETE: deletes one or more rows from a table. Docsity.com SQL: INSERT Statement To insert a row into a table, it is necessary to have a value for each attribute, and order matters. INSERT statement syntax: INSERT into <table name> VALUES ('value1', 'value2', NULL); Example: INSERT into FoodCart VALUES (’02/26/08', ‘pizza', 70 ); FoodCart 70 pizza 02/26/08 500 hotdog 02/26/08 350 pizza 02/25/08 sold food date 500 hotdog 02/26/08 350 pizza 02/25/08 sold food date Docsity.com SQL Statements, Operations, Clauses SQL Statements: Select SQL Operations: Join Left Join Right Join Like SQL Clauses: Order By Group By Having Docsity.com SQL: SELECT Statement A basic SELECT statement includes 3 clauses SELECT <attribute name> FROM <tables> WHERE <condition> SELECT Specifies the attributes that are part of the resulting relation FROM Specifies the tables that serve as the input to the statement WHERE Specifies the selection condition, including the join condition. Note: that you don't need to use WHERE Docsity.com Using a “*” in a select statement indicates that every attribute of the input table is to be selected. Example: SELECT * FROM … WHERE …; To get unique rows, type the keyword DISTINCT after SELECT. Example: SELECT DISTINCT * FROM … WHERE …; SQL: SELECT Statement (cont.) Docsity.com SQL: Join operation (cont.) inner join = join SELECT * FROM emp join dept (or FROM emp, dept) on emp.id = dept.id; Sales 1002 IT 1001 Dept.Division Dept.ID TN 1002 MA 1001 Emp.State Emp.ID Docsity.com SQL: Join operation (cont.) left outer join = left join SELECT * FROM emp left join dept on emp.id = dept.id; IT 1001 Sales 1002 null null Dept.Division Dept.ID CA 1000 TN 1002 MA 1001 Emp.State Emp.ID Docsity.com SQL: Join operation (cont.) right outer join = right join SELECT * FROM emp right join dept on emp.id = dept.id; Sales 1002 Biotech 1003 IT 1001 Dept.Division Dept.ID MA 1001 null null TN 1002 Emp.State Emp.ID Docsity.com SQL: The GROUP BY Clause The function to divide the tuples into groups and returns an aggregate for each group. Usually, it is an aggregate function’s companion SELECT food, sum(sold) as totalSold FROM FoodCart group by food; FoodCart 419 pizza 500 hotdog totalSold food 70 pizza 02/26/08 500 hotdog 02/26/08 349 pizza 02/25/08 sold food date Docsity.com SQL: The HAVING Clause The substitute of WHERE for aggregate functions Usually, it is an aggregate function’s companion SELECT food, sum(sold) as totalSold FROM FoodCart group by food having sum(sold) > 450; FoodCart 500 hotdog totalSold food 70 pizza 02/26/08 500 hotdog 02/26/08 349 pizza 02/25/08 sold food date Docsity.com SQL: Aggregate Functions Are used to provide summarization information for SQL statements, which return a single value. COUNT(attr) SUM(attr) MAX(attr) MIN(attr) AVG(attr) Note: when using aggregate functions, NULL values are not considered, except in COUNT(*) . Docsity.com