IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

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

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.