SQL Part 1

SQL Part 1

Learning Outcomes:

• Use SQL to maintain a simple relational database, and manipulate its data or retrieve the required information in at most three tables.

Skills:

• Modify the structure of the tables
• Add, delete and modify the data in the tables
• Create different views

Relevant past paper:

DSE ICT Elec A(SP-2016): SP 1cd. PP 1ai,iii,ci,iii. 2012 2ei. 2013 3b. 2014 2ab. 2015 2cii, 3di. 2016 2biii, 4ei.

AS CA(2000-2013): 2004 1bc. 2006 6dii. 2007 10ef. 2009 8e. 2010 3, 8c. 2011 10d. 2012 9e. 2013 9d,eii

Table structure related: CREATE, DROP, ALTER

CREATE:

Create a database:

• CREATE DATABASE <database name>;

Create a table:

• CREATE TABLE <table name>
  (<field name 1> <data type 1>, <field name 2> <data type 2>, …);

Create a view AS:

• CREATE VIEW <view name>
  AS SELECT <field name 1>, <field name 2> …
        FROM <table name>
        WHERE <conditions>;

1. Advantage of a view: better access control - database cannot be changed accidentally, data security - hiding confidential information from certain users, convenient to apply information search

Create an index ON:

• CREATE INDEX <index name>
  ON <table name> (<index field>);

1. How it works: works at WHERE clause. If index is created on the fields used in the WHERE clause, it can reduce the searching time. (most commonly used B-tree index, which will reduce the number of records to be examined - instead of examining all the records)
2. What fields to choose: frequently used fields, preferably containing different values
3. Advantage of an index: improves searching speed
4. Disadvantages: overhead on storage(extra storage space), index is rebuilt during insert, delete, update of records - slow down the process

DROP:

Delete a database:

• DROP DATABASE <database name>;

Delete a table:

• DROP TABLE <table name>;

Delete a view:

• DROP VIEW <view name>;

Delete a column:

• ALTER TABLE <table name>
  DROP COLUMN <field name>;

ALTER:

Change the data type of a field:

• ALTER TABLE <table name>
  ALTER COLUMN <field name> <new data type>;

Add a new field:

• ALTER TABLE <table name>
  ADD COLUMN <field name> <data type>;

Delete a field:

• ALTER TABLE <table name>
  DROP COLUMN <field name>;

Table content related: INSERT, DELETE, UPDATE

INSERT INTO: Insert a record

• INSERT INTO <table name>
  (<field name 1>, <field name 2> …)
  VALUES (<value 1>, <value 2> …);

1. Only some fields are filled in.

• INSERT INTO <table name>
  VALUES (<value 1>, <value 2> …);

1. All fields are filled in. The field names are not necessary, but the values should follow the order of the field names.

DELETE FROM: delete records

• DELETE FROM <table name>
  WHERE <conditions>;

UPDATE/SET: update the values of some fields

• UPDATE <table name>
  SET <field name 1> = <new value 1>,
           <field name 2> = <new value 2>,
           …
  WHERE <conditions>;

SUMMARY

Note the difference between changing table structure and table content.

TABLE STRUCTURE vs TABLE CONTENT

CREATE vs INSERT INTO

DROP vs DELETE FROM

ALTER vs UPDATE/SET