SQL Quries – DV Flexi Learning Program

SQL Queries

The SQL Mantra

Select * from table – Here is the SQL Mantra, which is the base and the most important part of the SQL queries – How do fetch all the values from a particular table?

Select * from table

To select all the columns from a particular table
Get the product table from classic models database

        select * from  table_name
        select * from classicmodels.products;

We will get the entire data of the table, “products”, which contains the product code, product name, product line, product sale, etc.

How do fetch a particular column from a particular table?

Select col_name from table

To select particular column from a particular table
Earlier it was “Select * from table_name”, where “*” means every column in table. So here we will mention the column name which we want from the table.
Get the productName column from products table in classic models database

        select col_name from  table_name
        select productName from classicmodels.products;

The output will have only that particular column that we mentioned.

How do fetch some specific columns from a particular table?

Select col_name1, col_name2 from table

To select multiple column from a particular table
It is similar to the earlier query i.e., instead of one column name enter the multiple columns names.
Get the productName and MSRP columns from products table in classic models database

        select col_name1, col_name2  from  table_name
        select productName,MSRP from classicmodels.products;

Can we assign alias while fetching the columns?

Select col_name as alias1 from table

Alias is used for renaming purpose.
To select particular column and assign alias
Get the product names as model_name from products table in classic models database

        select col_name as alias1 from  table_name
        select productName as model_name from classicmodels.products;

This query will fetch the column productName and rename it as model_name.

Select col_name as alias1 col_name2 as alais2 from table

To select multiple columns and assign relevant alias
Get the product names and MSRP columns from products table in classic models database. Give the alias model_name and final_price

        select col_name as alaias1 from  table_name
        select productName as model_name,MSRP as final_price from classicmodels.products

This will fetch productName and MSRP and rename it as model_name and final_price respectively.

How do you fetch data with condition?

Select * from table where condition

To select a table with some condition
Get the product table from classic models database select only records with product line “Motorcycle”

        select * from  table_name WHERE colname=value
        select * from classicmodels.products WHERE productLine='Motorcycles';

The above code will fetch all the record with productLine=Motorcycles.
Get the product table from classic models database select only records with MSRP more than 100

        select * from  table_name WHERE colname>xxx;  
        select * from classicmodels.products WHERE MSRP>100;

How do you fetch data with multiple conditions?

Select * from table WHERE condition AND

To apply multiple conditions, we can use AND operator between the conditions.
Get the product table from classic models database select only records with conditions product line=“Vintage Cars”” and MSRP is more than 100

        select * from  table_name WHERE condition1 and condition2
        select * from classicmodels.products WHERE productLine='Vintage Cars' and MSRP>100;

Select * from table WHERE condition OR

To select a table with WHERE conditions and “OR” operator
Get the product table from classic models database select only records with conditions quantityInStock>4000 OR MSRP>150

        select * from  table_name WHERE condition1 OR condition2
        select * from classicmodels.products WHERE quantityInStock>4000 OR MSRP>150;

In this query, we are trying to get all the records with productLine = “Vintage Cars” and MSRP>100.
For such situation of multiple conditions, we can either specify “and” or we can write “or”
If we say “or” any one condition which satisfies is enough, but if we mention “and” all the conditions has to satisfy.

Select * from table WHERE condition IN

To select a table with WHERE condition and “IN” operator
This query is used when we want to give multiple conditions for the same column
Get the product table from classic models database select only records where product line takes values “classic cars” or “Vintage cars” or “Trucks and Busses”

        select * from  table_name WHERE col_name IN (val1, val2)
        select * from classicmodels.products WHERE productLine IN ('Classic Cars','Vintage Cars', 'Trucks and Buses');

By typing in this query we get all the results with productLine value as “Classic Cars” or “Vintage Cars” or “Trucks and Buses”. So instead of writing the column name multiple times, we can use “IN” command, if all the conditions are for the same column.

Can we sort the data based on a column?

Select * from table ORDER BY

To select a table and sort it based on a column
Here we just have to say ORDER BY and then the column name which we want to take as reference for ordering or sorting and based on that, the table will be sorted in ascending or descending order.
Get the product table from classic models database make sure that the resultant table is sorted based on MSRP

        select * from  table_name ORDER BY col_name
        select * from classicmodels.products ORDER BY MSRP;

All the data or records from the products table will be sorted with respect to the MSRP column in the ascending order.

Select * from table ORDER BY descending

To select a table and sort it based on a column in descending order
Get the product table from classic models database make sure that the resultant table is sorted based on MSRP in descending order

        select * from  table_name ORDER BY col_name DESC
        select * from classicmodels.products ORDER BY MSRP DESC;

Can we fetch some summary statistics instead of data ?

Select Count(*) from table

To fetch number of rows
Get the row count of product table from classic models database

        select count(*) from table_name; 
        select count(*) from classicmodels.products;

This will give the total number of records or the total number of rows in the table.

Select Count(col_name) from table

To fetch number of rows based on a coloumn
Get the row count based on product code from product table in classic models database

        select count(col_name) from table_name; 
        select count(productCode) from classicmodels.products;

Select sum(col_name) from table where

To fetch the sum of values in a column
Get the sum of quantityInStock from product table in classic models database

        select sum(col_name) from table_name; 
        select sum(quantityInStock) from classicmodels.products;

Select avg(col_name) from table

To fetch the average of a column
Get the average of MSRP from product table in classic models database

        select avg(col_name) from table_name; 
        select avg(MSRP) from classicmodels.products;

Can we fetch some summary statistics grouped into segments. For example counts of east, west, north and south regions

Select count(*) from table group by col_name

To fetch count for groups of values in a column
What if there is a variable called region and for each region, i.e., east, west, south and north, we want to know the summary statistics of a particular column.
Get the counts of records for each product line. Frequency of each product line

        select col_name, count(*) from table_name GROUP BY col_name; 
        select productLine, count(*) from classicmodels.products GROUP BY productLine;

Select avg(col_name) from table group by col_name

To fetch average of a column for groups of values in a column
Get the average MSRP for each product line.

        select col_name1, avg(col_name2) from table_name GROUP BY col_name1; 

        select productLine, avg(MSRP) from classicmodels.products GROUP BY productLine;

GROUP BY and ORDER BY

Its is always better to order the results of the summaries that we get i.e., in ascending or descending order. For this purpose, we use a function called “order by”. -Get the average MSRP for each product line. Order the result based on average MSRP

        select productLine, avg(MSRP) as avg_price from classicmodels.products GROUP BY productLine ORDER BY avg_price

If we want to order it in descending order, then just mention DESC at the end.

Create Table

What if we want to save our results in a new table?
We just need to add “create table table_name as” before the entire query that we write.
Create a new table by selecting product line =Motorcycles. Name the new table as motor_cycles_table

        create table classicmodels.motor_cycles_table as 
        select * 
        from classicmodels.products 
        where productLine='Motorcycles';

Drop Table

Drop Statement

Use drop statement
Create a temp table by selecting MSRP>2000
Drop the temp table

        create table classicmodels.temp as select * from classicmodels.products where MSRP>2000;
        Drop table classicmodels.temp;