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 postalcode = "1010";

SELECT phone FROM Suppliers WHERE supplierid = "11"

SELECT * FROM Orders ORDER BY orderdate DESC

SELECT * FROM Suppliers WHERE length(CompanyName) > 20

SELECT * FROM Customers WHERE companyname LIKE '%market%' Don't forget the wildcard '%' symbols at the beginning and end of your substring to denote it can appear anywhere in the string in question

INSERT INTO Customers ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax ) VALUES ( 'CustomerID', 'The Shire', 'Bilbo Baggins', 'ContactTitle', '1 Hobbit-Hole', 'Bag End', 'Region', '111', 'Middle Earth', 'Phone', 'Fax' )

UPDATE Customers SET PostalCode = '11122' WHERE contactname = 'Bilbo Baggins'

SELECT Customers.CompanyName, COUNT(Orders.OrderID) AS Expr1 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CompanyName

There is more information about the COUNT clause on W3 Schools

SELECT Customers.CompanyName, COUNT(Orders.OrderID) AS Expr1 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CompanyName ORDER BY COUNT(Orders.OrderID) DESC

SELECT Customers.City, COUNT(Orders.OrderID) AS Expr1 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.City

DELETE FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders)

In the WHERE clause, you can provide another list with an IN keyword this list can be the result of another SELECT query. Write a query to return a list of CustomerIDs that meet the criteria above. Pass that to the IN keyword of the WHERE clause as the list of IDs to be deleted

Use a LEFT JOIN to join the Orders table onto the Customers table and check for a NULL value in the OrderID column

• 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.

This can be done with the CREATE TABLE clause id

CREATE TABLE accounts ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name STRING UNIQUE NOT NULL, budget DOUBLE NOT NULL ); CREATE INDEX names ON accounts ( name );