Year 9 - National Curriculum

Term 3

Unit - 5 Databases - SQL

A database, SOFASELECT, was set up to show the prices of suites, sofas and chairs for sale from an online furniture warehouse. Part of the database is shown below:

  1. Write SQL query to create the table SOFASELECT and enter the records.
  2. Write SQL query to show the brochure number, material, colour and price of all the furniture with 3 or more seats.
  3. Write SQL query to show the Description, Price and Colour of all the items of price less 1000
  4. Write SQL query to show all the items of Leather Material.

Solution:

  1. CREATE TABLE SOFASELECT ( Description VARCHAR(100), Brochure_Number VARCHAR(100), Number_of_seats INT(10), Number_of_pieces INT(10), Material VARCHAR(100), Colour VARCHAR(100), Price_in_$ INT(10) ); 
  2. INSERT INTO SOFASELECT VALUES("Sofa","SF17", 2,1,"Leather","Red",950); 
  3. SELECT Brochure_Number,Material,Colour,Price_in_$ FROM SOFASELECT WHERE Number_of_seats >= 3

A wildlife park has a database table, called LIVESTOCK, to classify and record its animal species. Part of the database table is shown.

  1. Write SQL query to create table LIVESTOCK and enter the records.
  2. Write SQL query to display the record of all the Mammals.
  3. Write SQL query to display Species and Diet of animals with only 2 legs.
  4. Write SQL query to display a list of all four legged mammals that are herbivores, sorted alphabetically by species, with only the species displayed.

6 A database table, JUICE, is used to keep a record of cartons of fresh fruit juice available for sale.

 

  1. Write SQL query to create table JUICE and enter the records.
  2. Complete the SQL query to display only the stock level and size of all cartons containing only apple juice.
  3. Write the SQL query to display the items with Stock less than 10.
  4. Write SQL query to display Juice code and Stock of all Large Size items. 

A database table, TREES, is used to keep a record of the trees in a park. Each tree is given a unique number and is examined to see if it is at risk of dying. There are over 900 trees; part of the database table is shown.

  1. Write SQL query to create table TREES and enter the records.
  2. Write SQL query to display the those trees who are not at risk.
  3. Write a query to identify at risk trees over 100 years old. Display only the type and the position on the map.

A database was set up to show the properties of certain chemical elements. Part of the database is shown below.

  1. Write SQL query to create table CHEMICAL and enter the records.
  2. Write SQL query to display Name of the element, Atomic weight and Atomic number of the elements with negative Melting point.
  3. Display the element symbol with state at room temperature is solid.

A database table, PERFORMANCE, is used to keep a record of the performances at a local theatre.

  1. Write SQL query to create table PERFORMANCE and enter the records.
  2. Write SQL query to display the performance that are Sold Out.
  3. Write a query to identify jazz performances that are not sold out. Only display the date and the title.