wiki.javier.io

This is an old revision of the document!

• Display the first and last name of all customers

SELECT FirstName, LastName FROM Customer

• Display the first and last name of all customers who are NOT from Paris

SELECT FirstName, LastName FROM Customer WHERE City <> 'Paris'

• Get the customer id and spend amount of all customers who have either spend less than $10 or more than $100

SELECT CustomerId, InvoiceTotal FROM Customer WHERE InvoiceTotal < 10 OR InvoiceTotal > 100

• Get the first, last name and city of customers who live in Brasil or are called 'Frank Sinatra'

SELECT FirstName, LastName, City FROM Customer WHERE Country = 'Brasil' OR (FirstName = 'Frank' AND LastName = 'Sinatra')

• Get the first name, city and total invoice of clients living neither in Paris, Prague nor Rome and whose invoices are between 35 and 40

SELECT FirstName, City, InvoiceTotal FROM Customer WHERE City NOT IN ('Paris', 'Prague', 'Rome') AND InvoiceTotal BETWEEN 35 AND 40

• Get the first name and invoice ordered first by invoice total descending and later by first name ascending.

SELECT FirstName, InvoiceTotal FROM Customer ORDER BY InvoiceTotal DESC, FirstName ASC

• Get the customer id, country and total invoice ordered by country first and later by total invoice, list first those customers living in Canada, USA or Mexico

SELECT CustomerId, Country, InvoiceTotal FROM Customer ORDER BY Country IN ('Canada,' 'USA', 'Mexico') DESC, InvoiceTotal

• Get the first and last name of customers whose first name doesn't start with P

SELECT FirstName, LastName FROM Customer WHERE  FirstName NOT LIKE 'P%'

• Get the first and last name of customers whose first name doesn't start with P, T or Z

SELECT FirstName, LastName FROM Customer WHERE  FirstName LIKE '[!P,T,Z]%'

• Get the first and last name of customers whose second name is made of four letters

SELECT FirstName, LastName FROM Customer WHERE LastName LIKE  '____'