SQL Queries

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.

About admin

Check Also

sql

Introduction to SQL- Basics

  SQL Basics Contents SQL Databases Sql Basics RMySql Sql Queries Sql Joins What is …

Leave a Reply

Your email address will not be published. Required fields are marked *