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