SQL

Question 1

Write a simple select query with a condition and sorting, no join

Answer 1

SELECT * 
FROM MyTable
WHERE MyColumn > 50
ORDER BY MyColumn

Question 2

Write a query with inner join

Answer 2

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Question 3

Create a table

Answer 3

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (PersonID)
);

Question 4

Insert row(s) to a table

Answer 4

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

-- If using all the columns you can eliminate the column names
INSERT INTO Customers
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

-- insert into with select
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

Question 5

Update data in a table row

Answer 5

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Question 6

Discuss Join types and show how to use each of them

Answer 6

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Question 7

Do a group by query with some aggregate

Answer 7

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Question 8

List SQL data types

Question 9

Show how to use a foreign key and discuss why it’s used

Question 10

Talk about indexes, give an example using one

Question 11

Use some common constraints and show how they are used.