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.
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;
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 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;
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.