SQL Tutorial: Creating and Manipulating a Database Table using SQL Commands, Lecture notes of Computers and Information technologies

Download SQL Tutorial: Creating and Manipulating a Database Table using SQL Commands and more Lecture notes Computers and Information technologies in PDF only on Docsity! 09/25/2019 Peter Dench 1 System Building 2 Lecture 5b Manipulating a Derby Database using SQL 09/25/2019 Peter Dench 2 SQL is a language used to manipulate Databases  This lecture will introduce some simple aspects of SQL (Structured Query Language)  You will not ‘know’ SQL at the end of this lecture  You will need to attend database courses to get a good/deep understanding  SQL is the industry standard for DB manipulation  At the end of the lecture you will be able to perform tasks via SQL scripts that in the past you may have used the GUI of your DB for.  SQL is commonly used from within another language (C++, Java…) where it is used to access a DB  These slides and accompanying .doc concentrate on the Apache Derby Database (which is firmly coupled to java). 09/25/2019 Peter Dench 5 If we wanted to create this table named RetailOutlet in a DB called PetersRetailDb outletName WeeklySales Date TopsToGo £1500 2016-12-03 BottomsRUS £250 2016-12-10 HatRack £300 2016-12-17 Gizzmos £700 2016-12-17 We would need to: 1 Connect to the DB system and Create PetersRetailDb 3 Create the RetailOutlet table 4 Insert the four records into the table CONNECT  Syntax:  CONNECT 'jdbc:derby:«DBName»;create=true’ ;  Example:  CONNECT 'jdbc:derby:PetersRetailDb;create=true’ ;     Note: Use without the create= true clause to re-connect to an existing database. 09/25/2019 Peter Dench 6 CREATE TABLE (also NOT, NULL, CHECK and DEFAULT)  Syntax:  CREATE TABLE «TableName» (« Fieldname1» «Type1» [«constraints1»] [, « Fieldname2» «Type2» [«constraints2» ]]…);  Example:  CREATE TABLE RetailOutlet (OutletName Char(20) NOT NULL, WeeklySales real CHECK (WeeklySales > 0.00) DEFAULT 0.01, Date Date);     Note:  The default of 0.01 above is unrealistic/stupid (it is just a demo)  Some databases would have a problem (get confused) with a fieldname of Date for a field (Date being a keyword for the type and here also used as a name for a field) 09/25/2019 Peter Dench 7 SELECT and FROM  Syntax:  SELECT «column_name1» [,«column_name2»]… FROM «table_name»;  Example:  SELECT outletName FROM RetailOutlet;    09/25/2019 Peter Dench 10 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 WHERE  Syntax: SELECT «column_name»[,«column_name2»]… FROM «table_name» WHERE «condition» ;  Example:  SELECT outletName, Date FROM RetailOutlet WHERE WeeklySales > 1000 ; 09/25/2019 Peter Dench 11 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 AND OR  Syntax: SELECT «column_name»[,«column_name2»]… FROM «table_name» WHERE «simple condition» [[AND | OR] «simple condition»]… ;  Example:  SELECT outletName FROM RetailOutlet WHERE WeeklySales > 1000 OR (WeeklySales < 500 AND WeeklySales > 275); 09/25/2019 Peter Dench 12 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 AVG  Syntax:  SELECT AVG(«column_name») FROM «table_name» ;  Example:  SELECT AVG(WeeklySales) FROM RetailOutlet ;  Note:  The column_name must be a numeric column 09/25/2019 Peter Dench 15 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 MAX  Syntax:  SELECT MAX(«column_name») FROM «table_name» ;  Example:  SELECT MAX(WeeklySales) FROM RetailOutlet ;  Note:  The column_name must be a numeric column 09/25/2019 Peter Dench 16 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 MIN  Syntax:  SELECT MIN(«column_name») FROM «table_name» ;  Example:  SELECT MIN(WeeklySales) FROM RetailOutlet ;  Note:  The column_name must be a numeric column 09/25/2019 Peter Dench 17 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 UPDATE  Syntax:  UPDATE «table_name» SET «column_1» = «value1»[, «column_2» = «value2»]… WHERE «condition» ;  Example:  UPDATE RetailOutlet SET WeeklySales = 500 WHERE outletName = ‘TopsToGo’ AND Date = ‘2016-12-17’ ; 09/25/2019 Peter Dench 20 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 And we end up with this table  SELECT * FROM RetailOutlet; 09/25/2019 Peter Dench 21 outletName WeeklySales Date TopsToGo £1500 2016-12-03 BottomsRUS £250 2016-12-10 HatRack £300 2016-12-17 Gizzmos £700 2016-12-17 TopsToGo £500 2016-12-17 DISTINCT  Syntax:  SELECT DISTINCT «column_name1» [,«column_name2»] … FROM «table_name»;  Example: SELECT DISTINCT outletName FROM RetailOutlet; 09/25/2019 Peter Dench 22 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 LIKE  Syntax:  SELECT «column_name1» [,«column_name2»]… FROM «table_name» WHERE «FieldName» LIKE «Pattern» ;  Example: SELECT * FROM RetailOutlet WHERE outletName LIKE '%RU%'; Note: The % is any number of characters so will match BottomsRUs 09/25/2019 Peter Dench 25 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 You can combine things  Examples: 1 SELECT outletName, SUM(WeeklySales) FROM RetailOutlet GROUP BY outletName HAVING SUM(WeeklySales) > 1300; 2 SELECT COUNT(DISTINCT outletName) FROM RetailOutlet; Note: 1 above will group outlets where total sales are > 1300 and list them and their sum of sales 2 above will return the number of different outletNames in the table 09/25/2019 Peter Dench 26 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 CREATE VIEW AS  Syntax: CREATE VIEW «viewName» AS SELECT «column_name1» [,«column_name2»]… FROM «table_name»;  Example: CREATE VIEW MyView AS SELECT outletName FROM RetailOutlet; 09/25/2019 Peter Dench 27 outletName WeeklySales Date TopsToGo £1500 03/Dec/2016 BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 TopsToGo £900 17/Dec/2016 JOIN  Syntax: SELECT «column_name1» [,«column_name2»]… FROM «table_name» JOIN «table_name» ON «Boolean Expression»  Example: SELECT S.EmpID, M.OutletName FROM Staff S JOIN Manager M ON S.EmpID = M.Manager; 09/25/2019 Peter Dench 30 outletName WeeklySales Date BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016 DROP  Syntax: DROP [«table_name» | «view_name»] ;  Examples: DROP TABLE RetailOutlet; DROP VIEW MyView; DROP TABLE Manager; DROP TABLE Staff; 09/25/2019 Peter Dench 31 outletName WeeklySales Date BottomsRUS £250 10/Dec/2016 HatRack £300 17/Dec/2016 Gizzmos £700 17/Dec/2016  You should try all of the above commands in the Derby DB  See this weeks Lab slot on teachmat 09/25/2019 Peter Dench 32