Sorting

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
LIMIT 3;

Functions

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
:id,name,category,downloads,price.

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;

Multiple Tables

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.

SQL Joins

Joins clause is used to combine records from two or more tables in a database.

We have CUSTOMER table:

id name age address Salary
1 Ram 23 Delhi 10000
2 Kavi 25 Chennai 40000
3 Jerry 44 Banglore 80000

Table of ORDER:

Order id date CUSTOMER ID Amount
100 2016-8-12 3 3000
101 2016-6-14 3 1500
102 2016-7-19 2 2000

 

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.

Result

id name age Amount
3 Jerry 44 3000
3 Jerry 44 1500
2 Kavi 25 2000

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.

Advertisements

One thought on “SQL-II

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s