Scribd
Upload
207 views2 pages

9618 - SQL - Exercises For 9618 Computer Science

The document outlines a series of SQL statements for Data Manipulation Language (DML) and Data Definition Language (DDL) tasks. DML tasks include listing client information, counting games, inserting, updating, and deleting client records, as well as aggregating game prices. DDL tasks involve creating a new database and tables, defining primary and foreign keys, and modifying table structures.

Uploaded by

Nathan M
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
207 views2 pages

9618 - SQL - Exercises For 9618 Computer Science

The document outlines a series of SQL statements for Data Manipulation Language (DML) and Data Definition Language (DDL) tasks. DML tasks include listing client information, counting games, inserting, updating, and deleting client records, as well as aggregating game prices. DDL tasks involve creating a new database and tables, defining primary and foreign keys, and modifying table structures.

Uploaded by

Nathan M
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Study the following Entity Relationship Diagram (ERD).

Then write SQL statements to solve the


questions below.

DML
1) List all columns from the Clients table

2) List all first names from the clients table in alphabetical order

3) Count all games with a price less than 10 EUR

4) Get the average and the sum of the prices of all the games

5) Insert a new client named ‘Chew Bacca’ with a ClientID of 212. Email address is
unknown.

6) Update the client ‘Chew Bacca’ and add the email address “[email protected]”

7) Delete the client with ClientID 207

8) Group the games by their genres and show the average price

9) List all ClientIDs and show the title of each game that they bought.

10) List all ClientIDs and show the total amount that each client spent (sum of prices)
DDL
1) Create a new database named Research.

2) Add a table named ‘ListOfExperiments’ with the following attributes ID (integer),


description (varchar(255)), date (date), time (time).

3) Add a table named ‘Results’ with attributes ID (integer), experimentID (integer), result
(real), success (BOOLEAN).

4) Make ID in the two tables primary keys.

5) Make experimentID into a foreign key.

6) Add a column named ‘category’ (char) to the Results entity

7) Rename the column to ‘class’

8) Change the type of the column from char to varchar(2)

9) Delete the column ‘class’

You might also like