SQL

Question 1

SQL
Insert a new partial record into the customer table using the data;

customer id 1234
customer forename Glenn
Customer surname Newman

Answer 1

INSERT INTO customer (customer_ID, forename, surname)

VALUES (“1234”, “Glenn”, “Newman”);

Question 2

SQL

A list of all customers names who travelled on 1st July 2012 in alphabetical order of surname

Answer 2

SELECT forename,surname
FROM customers, journey
WHERE Date_of_journey = #01/07/12
AND customers.customer_ID=Journey.customer_ID
ORDER BY surname;

Question 3

A list of all customers who travelled from Burnley to Preston ordered by driver

Answer 3

SELECT forename, surname
FROM customers, journey
WHERE start_location = “Burnley” AND end_location=”Preston”
AND customers.customer_ID=jouney.customerID
ORDER BY driver_ID;

Question 4

Insert a new partial record into the customer table using the data; customer id 1234 and the customer’s forename name is Glenn and the customer’s surname is Newman (5 marks)

Answer 4

INSERT INTO customers (customer_ID, forename, surname)

VALUES (“1234”, “Glenn”,”Newman”);

Question 5

A list of showing all customers who live in Croston

Answer 5

SELECT * (accept any suitable list of fields)
FROM customers
WHERE Town=”Croston”;

Question 6

All journeys in reverse date order

Answer 6

SELECT *
FROM journey
ORDER BY date_of_ journey DESC;

Question 7

A list of all Customer details in order of Customer Surname

Answer 7

SELECT*
FROM CUSTOMERS
ORDER BY Surname;

Question 8

A list of Order ids, date and values in order of order date

Answer 8

SELECT Order Ids, Order_Date, Order_Value
FROM Orders
ORDER BY Order_Date;

Question 9

A list of showing all customers who live in Lancashire grouped by Tow

Answer 9

SELECT*
FROM CUSTOMERS
WHERE TOWN=“Lancashire”;
GROUP BY Town;

Question 10

A list of all customers’ names who placed an order on 1st February 2013 in alphabetical order of surname

Answer 10

SELECT forename, surname 
FROM CUSTOMERS, Orders
WHERE Order_Date=“#01/02/13”
AND CUSTOMERS.Customer_ID=Orders.Customer_ID
ORDER BY Surname;

Question 11

A list of all customers who ordered on March 1st 2103 and the value was greater than £100, ordered by salesperson

Answer 11

SELECT forename, surename
FROM customers, orders
WHERE Order_Date=“#01/03/2103” AND Order_Value>”£100”
AND customers.customer_id = order.customer_ID
ORDER BY salesperson;

Question 12

Insert a new partial record into the customer table using the data; customer id 1234 and the customer’s forename name is Paul and the customer’s surname is Smith

Answer 12

INSERT INTO CUSTOMER(customer id, customer forename, customer surname)
VALUES (“1234”, “Paul”, “Smith”);