Scribd
Upload
76 views2 pages

9618 - SQL - Solutions For Computer Science

The document provides SQL statements for various data manipulation and definition tasks related to a database. It includes commands for selecting, counting, inserting, updating, and deleting records in the Clients and Games tables, as well as creating and modifying tables in a new database named Research. Additionally, it demonstrates how to manage primary and foreign keys, and alter table attributes.

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
76 views2 pages

9618 - SQL - Solutions For Computer Science

The document provides SQL statements for various data manipulation and definition tasks related to a database. It includes commands for selecting, counting, inserting, updating, and deleting records in the Clients and Games tables, as well as creating and modifying tables in a new database named Research. Additionally, it demonstrates how to manage primary and foreign keys, and alter table attributes.

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: Data Manipulation Language


1) List all columns from the Clients table
SELECT * FROM Clients;

2) List all first names from the clients table in alphabetical order
SELECT Firstname FROM Clients ORDER BY Firstname ASC;

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


SELECT COUNT(*) FROM Games WHERE Price < 10;

4) Get the average and the sum of the prices of all the games
SELECT AVG(Price), SUM(Price) FROM Games;

5) Insert a new client named ‘Chew Bacca’ with a ClientID of 212. Email address is
unknown.
INSERT INTO Clients (ClientID, Firstname, Surname)
VALUES (212, “Chew”, “Bacca”);

6) Update the client ‘Chew Bacca’ and add the email address “chewie@[Link]”
UPDATE Clients SET Email=”chewie@[Link]”
WHERE ClientID = 212;

7) Delete the client with ClientID 207


DELETE FROM Clients WHERE ClientID = 207;

8) Group the games by their genres and show the average price
SELECT AVG(Price) FROM Games GROUP BY Genre;

9) List all ClientIDs and show the title of each game that they bought.
SELECT [Link], [Link] FROM
Orders INNER JOIN Games ON [Link] = [Link];

10) List all ClientIDs and show the total amount that each client spent (sum of prices)
SELECT [Link], SUM(Price) FROM
Orders INNER JOIN Games ON [Link] = [Link]
GROUP BY [Link];
DDL: Data Definition Language
1) Create a new database named Research.
CREATE DATABASE Research;

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


description (varchar(255)), date (date), time (time).
CREATE TABLE ListOfExperiments(
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).
CREATE TABLE Results(
ID INTEGER,
experimentID INTEGER,
result REAL,
success BOOLEAN
);

4) Make ID in the two tables primary keys.


ALTER TABLE ListOfExperiments ADD PRIMARY KEY (ID);
ALTER TABLE Results ADD PRIMARY KEY (ID);

5) Make experimentID into a foreign key.


ALTER TABLE ListOfExperiments
ADD FOREIGN KEY (experimentID) REFERENCES Results(ID);

6) Add an attribute named ‘category’ (char) to the Results entity


ALTER TABLE Results ADD category CHARACTER;

7) Rename the attribute to ‘class’


ALTER TABLE Results RENAME COLUMN category TO class;

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


ALTER TABLE Results ALTER COLUMN class VARCHAR(2);

9) Delete the attribute ‘class’


ALTER TABLE Results DROP COLUMN class;

You might also like