To sort data in SQL we use ORDER BY clause
If to sort the movies in descresing order of box office rating type:
SELECT* FROM MOVIES
ORDER BY box office rating DESC;
DESC here implies descending order.
To query that returns the three lowest rated movies
SELECT * FROM movies
ORDER BY imdb_rating ASC
We use functions to quickly sum,average ,count a particular column in the table.
Suppose we take the database of mobile applications that includes columns
To view the table including the column of price of application along with its price frequency with the condition of downloads >20000 we type
SELECT price, COUNT(*) FROM mobile_apps
WHERE downloads > 20000
GROUP BY price;
In order to count the application with price 0 we type
SELECT COUNT(*) FROM mobile_apps WHERE price = 0;
To Sum the downloads of each applications we use
SELECT SUM(downloads) FROM mobile_apps ;
To know number that represent maximum download of application
SELECT MAX(downloads) FROM mobile_apps;
Similarly to get the number that represent the minimum download we use
SELECT MIN(downloads) FROM mobile_apps;
To get the average number of downloads we use
SELECT AVG(downloads) FROM mobile_apps;
To round the average number of downloads to two decimal places for each price we use
SELECT price, ROUND(AVG(downloads), 2) FROM mobile_apps GROUP BY price;
It is possible that two or more tables are related to each other .Through SQL we can combine the data from the tables that are related to each other.
Suppose we already have a table named albums,we now create a second table named artists by typing CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT);
We must know that an artist can create many albums but an album is produced by an artist.
id here is a primary key means that SQL ensures that none of the values in this column are NULL and each value is the unique .
To look at both the tables of artist and albums we must type the following:
SELECT * FROM artists WHERE id = 3;⇐ Table of artists
SELECT * FROM albums WHERE artist_id = 3;⇐Table of albums
artist_id: column of id of artist in the albums table. id in the artists table is same as artist_id in the albums table.
Joins clause is used to combine records from two or more tables in a database.
We have CUSTOMER table:
Table of ORDER:
|Order id||date||CUSTOMER ID||Amount|
Joining the two tables :
SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Here CUSTOMERS.ID = ORDERS.CUSTOMER_ID depicts the relation between the id column in customers table and customer id in Orders table.
There are many joins In SQL:
INNER JOIN: selects all rows from both tables as long as there is a match between the columns in both tables.
LEFT JOIN : Select all rows from the table 1, with the matching rows in the table 2. The result is NULL in the right side when there is no match.
RIGHT JOIN: Selects all rows from the table 2 , with the matching rows in the table 1 . The result is NULL in the left side when there is no match.
FULL JOIN : Selects all rows from the table 1 and from the table 2 or combines the result of both LEFT and RIGHT joins.
This is an end to the into to SQL . Once gone through the basics we can easily run the complex commands as well.