Classnotes

Database Languages

eXercise #5

---

microSQL TABLES, VIEWS and ALIASES

1. SET the PATH to the appropriate DATA directory and LOAD the MUSIC database

   In the EDITor, write a CREATE TABLE command that will bring into existence a table that is described below:

   MYCDS

   sernum	albname	artist	pdate
   char(15) ma	char(25) op	char(20) op	dec(4) ma
   KEY

   Once you have created this table, perform a DESCribe DATAbase command to ensure it exists.

   Populate the table with values from the albums table that are albums owned on the media 'CD', using a variation of the following syntax:

   
   	insert into new_tabname
   	select 	col_name {,col_name}
   	from	base_tabname
   	where	base_table_row_condition
   

   Remove from the mycds table, all tuples that involve the artist named 'Violent Femmes', with a variation on the following syntax:

   
   	delete 	from tab_name
   	where	col_name = value
   

   One of the tuples in the mycds table needs a name change. Use a variation on the following syntax:
   

   
   	update table tab_name
   	set 	col_name = value
   	where	row_condition
   

   
   to change the artist named 'Laurie Anderson' to 'Lorrie Andreason' - check to make sure the change has been made.

   Use a delete from command to completely empty the mycds table (check it has been done)

   DROP the mycds TABLE, and DESCribe DATAbases to confirm its demise.

2. Use a variation on the following syntax:
   

   
   	create synonym name_2 for tab_name;

   
   to create a copy of the ALBUMS table, then test it exists by performing a few of the queries as listed on the previous exercise sheet.

   DROP your synonym once you have finished with it.

3. Use a variation on the following syntax:
   

   
   	create view view_name (newcol_name {,newcol_name})
   	as  select  col_name {,col_name}
   	    from tab_name;

   
   to create a view that contains the following columns: p_date, alb_name and artist in that order - perform an unconditional select on this view to convince yourself it exists.

   COMMIT the view once you are happy with it.

4. CREATE a table definition called REC_ORIGIN that contains the following columns: rec_name char(30), performed_by char(30), released_by char(15), released_on dec(4) , whose PRIMARY KEY is a combination of rec_name, performed_by and released_on

   DESCRIBE TABLES rec_origin to check it exists

   POPULATE the table with row instances from the albums table that are ON CASSETTE ONLY using a variation on the following syntax:
   

   
   	insert into tab_name (col_name {,col_name})
   	  select col_name {,col_name}
   	  from tab_name
   	  where  row_condition

   
   and check your resultant population using conditional select statements