Working with SQL

Surf to SQL Try Editor at W3Schools.com
Answer the following data queries. Keep track of the SQL you write by pasting it into this document under its appropriate header below. You will be submitting that through the regular fork, change, pull process.

• SELECT * FROM customers WHERE city = "London"

• SELECT * FROM customers WHERE city = "London"

• SELECT * FROM suppliers WHERE supplierID = 11

• SELECT * FROM Orders ORDER BY orderdate desc

• SELECT * FROM suppliers WHERE len(suppliername) > 20

• SELECT * FROM customers WHERE customername LIKE "%market%"

• INSERT INTO customers (contactname, address, city, postalcode, country)
• VALUES ("BilboBaggins", "1 Hobbit-Hole", "Bag End", "111", "Middle Earth")

• UPDATE customers
• SET postalcode = "11122"
• WHERE contactname = "BilboBaggins";

• SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
• LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
• GROUP BY CustomerName

• SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
• LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
• GROUP BY CustomerName
• ORDER BY COUNT(Orders.OrderID) DESC;

• SELECT COUNT(Orders.OrderID) AS NumberOfOrders, Customers.City
• FROM Orders
• LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
• GROUP BY City
• ORDER BY city asc;

• DELETE FROM customers WHERE CustomerID NOT IN (SELECT CustomerID from orders)

• use SQLite Studio to create a database, name it budget.sqlite3.

• add an accounts table with the following schema:

  • id, numeric value with no decimal places that should autoincrement.
  • name, string, add whatever is necessary to make searching by name faster.
  • budget numeric value.

• constraints

  • the id should be the primary key for the table.
  • account name should be unique.
  • account budget is required.