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!

System Building 2

Lecture 5b

Manipulating a Derby Database

using SQL

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).

SQL is a powerful tool for

manipulating data within a

Database

There are many SQL keywords we could use to manipulate a table some of which are: CREATE, SELECT, INTO, UPDATE, FROM, WHERE, AND, OR, ORDER, BY, ASC, DESC, GROUP, COUNT(), SUM(), INSERT, VALUES, SET, DROP, TRIM, INT, REAL, DATE, CHAR, NOT, NULL, CHECK, DEFAULT, VIEW, DELETE, JOIN …

If we wanted to create this

table named RetailOutlet in a

DB called PetersRetailDb

outletName WeeklySales Date TopsToGo £1500 2016-12- BottomsRUS £250 2016-12- HatRack £300 2016-12- Gizzmos £700 2016-12- 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

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)

INSERT, INTO, and VALUES

 (^) Syntax:  (^) INSERT INTO «TableName »( «FieldName1» [, «FieldName2»]… ) VALUES («FieldValue1» [, «FieldValue2»]… ) ;  (^) Example:  (^) INSERT INTO RetailOutlet (OutletName, WeeklySales, Date ) VALUES ('TopsToGo',1500,'2016-12-03'); INSERT INTO RetailOutlet (OutletName, WeeklySales, Date ) VALUES ('BottomsRUs',250,'2016-12-10'); INSERT INTO RetailOutlet (OutletName, WeeklySales, Date ) VALUES ('HatRack',300,'2016-12-17'); INSERT INTO RetailOutlet (OutletName, WeeklySales, Date ) VALUES ('Gizzmos',700,'2016-12-17'); INSERT INTO RetailOutlet (outletName, WeeklySales, Date) VALUES ('TopsToGo', 900, '2016-12-17');  (^) Note:  (^) You need one INSERT INTO per row/record 09/25/2019 Peter Dench 8

SELECT and FROM



Syntax:



SELECT «column_name1» [,«column_name2»]…

FROM «table_name»;



Example:



SELECT outletName

FROM RetailOutlet;

 outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

WHERE

Syntax:

SELECT «column_name»[,«column_name2»]…

FROM «table_name»

WHERE «condition» ;

Example:

SELECT outletName, Date

FROM RetailOutlet

WHERE WeeklySales > 1000 ;

outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

ORDER BY

 (^) Syntax:  SELECT «column_name» [,«column_name2»]… FROM «table_name» [WHERE «condition»] ORDER BY «column_name1» [ASC | DESC] [, «column_name2» [ASC | DESC]]…;  Example:  SELECT outletName, WeeklySales, Date FROM RetailOutlet ORDER BY WeeklySales DESC; ASC (default) could be used in a similar way outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

COUNT



Syntax:



SELECT COUNT(«column_name»)

FROM «table_name» ;



Example:



SELECT COUNT(outletName)

FROM RetailOutlet ;

outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

MAX



Syntax:



SELECT MAX(«column_name»)

FROM «table_name» ;



Example:



SELECT MAX(WeeklySales)

FROM RetailOutlet ;



Note:



The column_name must be a numeric column

outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

MIN



Syntax:



SELECT MIN(«column_name»)

FROM «table_name» ;



Example:



SELECT MIN(WeeklySales)

FROM RetailOutlet ;



Note:



The column_name must be a numeric column

outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

GROUP BY

 Syntax:  SELECT «column_name1», SUM(«column_name2») FROM «table_name» GROUP BY «column_name1» ;   (^) Example:  SELECT outletName, SUM(WeeklySales) FROM RetailOutlet GROUP BY outletName ; outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/

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’ ; outletName WeeklySales Date TopsToGo £1500 03/Dec/ BottomsRUS £250 10/Dec/ HatRack £300 17/Dec/ Gizzmos £700 17/Dec/ TopsToGo £900 17/Dec/