Question

Question 6        (Marks: 35) Structured Query Language (SQL) is a language that is widely used in industry to create, update and query data in relational databases. This question must NOT be done practically (i.e. in the computer room). You are required to write the SQL code in your answer book. Q.6.1 The below sample data in third normal form was provided by a database designer. Answer the below questions using this data. Table: Country Primary key: CountryID (auto number) All fields are mandatory CountryID Name Abbreviation CallingCode 1 South Africa ZA 27 2 Lesotho LS 266 3 Namibia NA 264 4 Egypt EG 20 Table: President Primary key: PresidentID (auto number) Foreign key: CountryID (mandatory) All fields are mandatory PresidentID CountryID Name Surname Year 1 1 Cyril Ramaphosa 2018 2 1 Jacob Zuma 2009 3 3 Hage Geingob 2015 4 3 Hifikepunye Pohamba 2005 18; 19; 20                       2020 © The Independent Institute of Education (Pty) Ltd 2020   Page 10 of 11    Q.6.1.1 Write a SQL statement to create the table President. Hint: The sample data should give you an indication of the data types you should use. (5)      Q.6.1.2 Write a SQL statement that will count the number of presidents that were inaugurated after 2009. (4)      Q.6.1.3 Write a SQL statement to insert the below row into table Country. CountryID Name Abbreviation CallingCode 5 Botswana BW 267 (4)      Q.6.1.4 Write a SQL statement to get the list of all the countries from the database, in alphabetical order by country name. Include all the columns from the Country table. (3)      Q.6.1.5 Write a SQL statement to get the list of all the presidents with a surname starting with the letter R. Include all fields from the President table. (3)      Q.6.1.6 Write a SQL statement to get the list of all the presidents, showing only the name and surname of the president, and the name of their country. (5) Q.6.2 What is the difference between the WHERE and HAVING clauses in SQL statements? (4) Q.6.3 What is the purpose of an index in a SQL database? (1) Q.6.4. The below ERD has been implemented in a SQL database. What will the result be of each of the below queries? Provide an explanation for your answer.   18; 19; 20                       2020 © The Independent Institute of Education (Pty) Ltd 2020   Page 11 of 11 Q.6.4.1 (2)      Q.6.4.2 (2)      Q.6.4.3  

Answer 1

Assumption:

For the last question  Q.6.4. ERD is not given and the question is incomplete.

Q.6.1.1 Write a SQL statement to create the table President.

     CREATE TABLE President (
        PresidentID        INT           NOT NULL ,                    
        CountryID          INT           NOT NULL,     
        Name               VARCHAR2(50)  NOT NULL,      
        Surname            VARCHAR2(50)  NOT NULL,
        Year               INT           NOT NULL,      
        CONSTRAINT President_PK PRIMARY KEY(PresidentID),
        CONSTRAINT President_FK1 FOREIGN KEY(CountryID) REFERENCES Country(CountryID)
        );

The table is craeted with the mentioned Primary Key and Foreign Key and the datatypes are gives based on the data that we have.

Q.6.1.2 Write a SQL statement that will count the number of presidents that were inaugurated after 2009. (4)

select * from President where year > 2009;

We have two records for the presidents who were inaugurated after 2009.

select count(*) from President where year > 2009;

Since it is mentioned 'After 2009',Records of 2009 should not be included.

Q.6.1.3 Write a SQL statement to insert the below row into table Country.
CountryID Name Abbreviation CallingCode
5 Botswana BW 267 (4)   

INSERT INTO Country
VALUES(5,'Botswana','BW',267);

Q.6.1.4 Write a SQL statement to get the list of all the countries from the database, in alphabetical order by country name.
Include all the columns from the Country table. (3)

select * from country order by name ;

'Order by country' indicates that the query has to be sorted by increasing alphabetical order of Country names.No need to specify 'ASC' for Ascending.

Q.6.1.5 Write a SQL statement to get the list of all the presidents with a surname starting with the letter R.
Include all fields from the President table. (3)

     select * from president where surname like 'R%' ;

'R%' indicates that the surname should start with R.'%' represents any number of any characters.

Q.6.1.6 Write a SQL statement to get the list of all the presidents, showing only the name and surname of the president, and the name of their country. (5)

select p.name,surname,c.name 
from
country c,president p
where c.countryid=p.countryid;

The country and President tables have countryID as common since countryID in president table refers to the countryID in Country table.

So both the tables can be joined with the common column to fetch columns from both the tables.

Q.6.2 What is the difference between the WHERE and HAVING clauses in SQL statements? (4)

Both HAVING and WHERE are used for filtering the query based on certain condition.

WHERE can be used in any queries where a filteration is needed based on a particular condition.

HAVING can be used only with GROUPED elements(to the rows that resulted as a part of grouping).That is,The query should have a GROUP BY clause .

A query can have both WHERE and HAVING clause at a time.

Q.6.3 What is the purpose of an index in a SQL database? (1)

Indexes are mainly used to speedup the querying time.When you use Index in a database you dont have to search each and every row to locate data.

Indexes make querying efficient and faster enhancing the entire system.