SQL Basics

Question 1

Creating Tables

Answer 1

CREATE TABLE Students(name VARCHAR(20), identification INT)

Question 2

Constraints

Answer 2

CONSTRAINT PRIMARY KEY (identification)

Question 3

Dropping Tables

Answer 3

DROP TABLE Students;

Question 4

Inserting into tables

Answer 4

INSERT INTO Students(name, identification) VALUES (‘Jake’, 204902);

Question 5

Selecting Values

Answer 5

SELECT * FROM Students;

Question 6

Modifying Tables

Answer 6

ALTER TABLE Students ADD email VARCHAR(100);
ALTER TABLE Students MODIFY email VARCHAR(200);

Question 7

Deleting Values

Answer 7

DELETE FROM Students WHERE name=’Jake’

Question 8

Updating Values

Answer 8

UPDATE Students SET identification=20139 WHERE name=’Jake’

Question 9

Conditions

Answer 9

• NOT -> use everything except what is specified
• BETWEEN -> between integers (10 and 20 etc)
• LIKE -> for string matching

Question 10

Projection

Answer 10

Allows you to choose what to select (SELECT * ,SELECT Events etc. You could have SELECT Grades, Name, aka multiple).

Question 11

Distinct

Answer 11

Removes duplicate rows (SELECT DISTINCT.

Question 12

Renaming

Answer 12

Allows you to rename attributes (SELECT Event AS Activity, SELECT first_name, family_name AS Surname)

Question 13

New Columns

Answer 13

Using math and AS to create new columns (SELECT Price * Number AS Total_cost).

Question 14

Cross Product

Answer 14

In a way, its joining tables (SELECT first_name, grade FROM Students, Results).

Question 15

Natural Join

Answer 15

Another way of doing it (SELECT * FROM Employees NATURAL JOIN Transactions)

Question 16

Removing Duplicates

Answer 16

DISTINCT

Question 17

WHERE Statements (WHERE IN, WHERE EXISTS)

Answer 17

SELECT * FROM Students WHERE name IN(‘John’, ‘Sam’). You could also use IN(SELECT name FROM Lecturers) instead of having to provide all the names.

SELECT * FROM Students WHERE EXISTS (SELECT 1 FROM Lecturers WHERE Students.name=Lecturers.name AND Students.last_name=Lecturers.last_name). A generalisation of IN and is used to find multiple.

Question 18

Grouping

Answer 18

• Shows us how many things are connected to what is specified (SELECT id, count FROM Transactions GROUP BY id - this would show the amount of connections id has and store them in count).

Question 19

Ordering

Answer 19

ORDER BY.

Question 20

Views

Answer 20

• CREATE OR REPLACE VIEW - this would either create the virtual table or simply rewrite it with whatever is specified.
• DROP VIEW… -> drops the table

Question 21

Combining Two Tables Sequentially

Answer 21

UNION:
Combines two tables together into one output.
select (contactFirstName + ‘ ‘ + contactLastName) as contactName FROM customers UNION
select customerName FROM customers