What is an SQL?

SQL  which stands for ‘Structured Query Language’, is a programming language designed to manage data stored in relational databases.

Relational Database- When data is organised in the form of tables.

In order to create table of celebs containing the celebrities id,names,age using SQL following are the commands:

CREATE TABLE  table_name(column_1 datatype,column_2 datatype,column_3 datatype)

Here CREATE TABLE students (id INTEGER,name TEXT,age INTEGER)

Here CREATE TABLE  is a clause or commands;it should be in capitals

Rows And Columns

To Insert rows in table of students we use the commands

INSERT INTO students (id, name, age) VALUES (1, ‘Ram’, 21);

To view the row you just created, under the INSERT statement type

SELECT * FROM students;

(*): makes sure that every column is included in the given table.

Similarly add two more rows to the table by typing:

INSERT INTO students(id, name, age) VALUES (2, ‘Tanya’, 33);

INSERT INTO students(id, name, age) VALUES (3, ‘Jerry’, 26);

To view the table type again SELECT * FROM students;

EDIT ROWS :  To edit or update the information given in rows just type

Here in case of students table, we want to change the age of Ram to 26.

UPDATE table_name
SET age = 26
WHERE id = 1;

Again to view the table use the very same command which show you the table with updated information SELECT * FROM celebs;

Add Columns

To add column in the table type ALTER TABLE table name ADD COLUMN column name TEXT;

ALTER TABLE is a clause that lets you make the specified changes.

ADD COLUMN is a clause that lets you add a new column to a table.

TEXT is the data type for the new column

It is possible that there is no value given in the added column ,in order to delete that students name we type :

DELETE FROM table name WHERE column_name IS NULL;

Here NULL is a special value in SQL that represents missing or unknown data.

QUERIES

Queries are the one that communicate with the database by asking questions and having the result  relevant to the question.

Suppose we take the database of movie with which we query.

If we want to view names and box office rating of the movie we use

SELECT name, box office rating FROM movies;

To list each genre in the movies  we use SELECT DISTINCT genre FROM movies;

SELECT DISTINCT ensure each genre is listed once given the above command.

genre-column name to be displayed.

Querying With Conditions

Suppose you want to filter the result set to include only rows where the given condition is true.

Condition : Select movies whose box office rating >6

To view the same in tables where the given condition is satisfied we use

SELECT * FROM movies WHERE box office rating>6;

WHERE is a clause here that keeps only those rows in the table where the above condition is satisfied.

Similarly we can filter movies with different clauses.

Suppose you want to view movie whose name starts with ‘a’,then we use

SELECT * FROM movies WHERE name LIKE ‘a%’;

LIKE is an operator used to compare similar values.

‘a%’ matches all movies with names that begin with “A”.

If we want to filter the movies which releases in the year between 1990 to 2000

we can do that simply by typing:

 SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;

If there are two conditions to be satisfied use AND clause

SELECT * FROM movies WHERE year BETWEEN 1990 and 2000 AND genre = ‘thriller’

Either conditions to be satisfied

Condition 1 : genre=thriller

Condition 2: year<1980

Type:  SELECT * FROM movies WHERE genre = ‘comedy’ OR year < 1980;

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

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