SQL Tutor

SELECT name, salary FROM employee ORDER BY name, salary DESC;

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it

The following SQL statement selects all customers with a City starting with the letter "s":

SELECT * FROM Customers WHERE City LIKE 's%';

The following SQL statement selects all customers with a Country containing the pattern "land":

SELECT * FROM Customers WHERE Country LIKE '%land%';

Using the NOT keyword allows you to select records that does NOT match the pattern. The following SQL statement selects all customers with a Country NOT containing the pattern "land":

SELECT * FROM Customers WHERE Country NOT LIKE '%land%';

Select all customers that are not associated with a company

SELECT * FROM customers WHERE company IS null

Give me a list of all artists sorted alphabetically by first name

SELECT * FROM employees ORDER BY hire_date DESC

All employees whose first name starts with an 'A'

SELECT * FROM employees WHERE first_name LIKE 'A%'

Give me a list of the 10 most expensive invoices from Boston, MA

SELECT * FROM invoices WHERE billing_city = 'Boston' ORDER BY total DESC LIMIT 10

Give me a list of the 10 least expensive invoices from Boston, MA

SELECT * FROM invoices WHERE billing_city = 'Boston' ORDER BY total LIMIT 10

Give me a list of all invoices from either Cupertino, CA or Mountain View, CA

SELECT * FROM invoices WHERE billing_city = 'Cupertino' OR billing_city = 'Mountain View';

Give me a list of all invoices from Redmond, WA sorted from low-to-high by total

SELECT * FROM invoices WHERE billing_city = 'Redmond' ORDER BY total

Give me a list of all invoices from Redmond, WA sorted from high-to-low by total

SELECT * FROM invoices WHERE billing_city = 'Redmond' ORDER BY total DESC

Give me a list of all invoices from Berlin, Germany

SELECT * FROM invoices WHERE billing_city LIKE '%erlin%'

Give me a list of the 10 most expensive invoices from Germany

SELECT * FROM invoices WHERE billing_country = 'Germany' ORDER BY total DESC LIMIT 10 Note: LIMIT determines how many

IS null

SELECT * FROM tracks WHERE composer IS null Note: Brings back ones that have no composer

Find the number of invoices sent to the city of "Santiago"

SELECT COUNT (*) FROM invoices WHERE billing_city = 'Santiago' Notice: Count(___) <= returns a number

SQL INNER JOIN Example The following SQL statement will return all customers with orders:

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

Give me a list of the top 5 cities by number of invoices in descending order

SELECT billing_city, count(*) FROM invoices GROUP BY billing_city ORDER BY count(*) DESC LIMIT 5

Give me a count of the number of employees by city

SELECT city, count(*) FROM employees GROUP BY city

The Syntax for joining two tables is:

SELECT col1, col2, col3... FROM table_name1, table_name2 WHERE table_name1.col2 = table_name2.col1;

WHERE clause

SELECT column_list FROM table-name WHERE condition;

Give me a count of the number of customers by country

SELECT country, count(*) FROM customers GROUP BY country

Give me a list of the top 3 countries by number of customers in descending order

SELECT country, count(*) FROM customers GROUP BY country ORDER BY count(*) DESC LIMIT 3

Give me a list of all names of artists whose name contains "smith".

SELECT name FROM artists WHERE name LIKE '%smith%'

Give me a list of all names of artists whose name ends with "smith".

SELECT name FROM artists WHERE name LIKE '%smith'

sort the employee table by the name and salary

SELECT name, salary FROM employee ORDER BY name, salary;

Give me a count of the number of tracks by unit price

SELECT unit_price, count(*) FROM tracks GROUP BY unit_price

SQL Joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

What is SQL?

Structured Query Language. SQL is used to communicate with a database. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

%

The "%" sign is used to define wildcards (missing letters) both before and after the pattern. You will learn more about wildcards in the next chapter.

SQL ORDER BY

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts query results in ascending order by default.

SQL GROUP BY Clause

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.

WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other class. Retrieving information about all the students would increase the processing time for the query.

SELECT dept, SUM (salary) FROM employee GROUP BY dept;

dept salary ---------------- -------------- Electrical 25000 Electronics 55000 Aeronautics 35000 InfoTech 30000 Notice: grouped by department