SQL Programming Tutorial for Interview Preparation 2020

SQL programming is very simple to learn because it uses English like syntax. SQL, Structured Query Language, helps to retrieve data or manage data or create an interface with a relational database.  It is used as a language which can communicate with the data stored in relational databases. SQL is usually asked in IT interviews, therefore we have created an easy to learn tutorial for interview preparation in 2019.

Share This 

SQL Programming Tutorial for Interview Preparation 2019

SQL was developed at IBM in the 1970s and nowadays it is very popular. Earlier, We wrote many posts on programming Interview preparation. To prepare for interviews, read our posts on PHP, Python, Java, C, and general interview preparation. This is our another post on SQL programming to help you prepare for interviews 2019.

In SQL programming, the main task is to learn SQL commands. As we already discussed above that this programming language uses English like Syntax. Therefore, learning is quite simple as compared with other languages. We created this post to help you learn SQL fast which will help you excel in interviews 2019.

 

 

SQL COMMANDS

SQL commands are the base for SQL programming. This helps to retrieve data and communicate with the data stored in relational databases. We have listed several commands with their syntax and explanation. Read>

 

SELECT

The SELECT command is used to select and return a particular data from a database. An Asterisk “*” is used just after SELECT to select all the columns in the table. It means the complete table will be selected if we use an asterisk after SELECT. The syntax is:-

 

SELECT * FROM table_name;

or

SELECT Column_name FROM table_name;

 

SELECT DISTINCT

The SELECT DISTINCT command is used to select only distinct data or different values which don’t match with other values from the table. A table may contain duplicate values if we need only distinct values from a table to be returned then, we use SELECT DISTINCT statement. The syntax is:-

 

SELECT DISTINCT Column_name FROM table_name;

 

SQL WHERE

The WHERE clause is used in SQL programming to return only the records which satisfy the given condition. Thus, this is used to filter records from a database. The syntax is:-

 

SELECT column_name FROM table_name WHERE condition;

 

SQL AND, OR and NOT Operators

AND, OR and NOT operators are the same boolean operators you have used in other programming languages. The WHERE clause is used here to provide conditions with AND, OR, and NOT operators. Basically, these operators are used to filter the records based on conditions. The AND operator returns a record for all the conditions (Condition1, Condition2, Condition3,….) separated by AND operator is TRUE. Whereas, the OR operator returns a record for any one of the conditions is TRUE out of two or more conditions. The NOT operator returns a record where the condition is FALSE. The syntax for AND, OR and NOT operators is:-

 

SELECT column_name FROM table_name WHERE condition1 AND condition2 ;

SELECT column_name FROM table_name WHERE condition1 OR condition2 ;

SELECT column_name FROM table_name WHERE NOT condition;

 

ALTER Table

ALTER TABLE command helps to add a column to the given table. Thus, this helps to change or alter the table. The syntax is:-

 

ALTER TABLE table_name 

ADD column_name datatype;

 

AS

AS command helps to rename a column or table in a database using an Alias_name. The syntax is:-

 

SELECT column_name AS 'Alias_name'

FROM table_name;

 

AVG()

AVG()function helps numeric columns to return an average value of columns. The syntax is:-

 

SELECT AVG(column_name)

FROM table_name;

 

BETWEEN

In SQL programming, you sometimes require to display the result that lies within a certain range. The range of values can be text, numbers, or dates. The syntax is:-

 

SELECT column_name

FROM table_name

WHERE column_name BETWEEN value_1 AND value_2;

 

CASE

The CASE statement is used when you have different conditions and different result set corresponding to each condition. The syntax is:-

 

SELECT column_name,

CASE

WHEN condition THEN 'Result_1'

WHEN condition THEN 'Result_2'

ELSE 'Result_3'

END

FROM table_name;

 

COUNT()

COUNT() function counts the number of rows in a column where the column is not NULL. The syntax is:-

 

SELECT COUNT(column_name)

FROM table_name;

 

CREATE TABLE

In SQL programming, you need to create a table in the database. CREATE TABLE statement helps in creating a new table. The syntax is:-

 

CREATE TABLE table_name 

(

  column_1 datatype,

  column_2 datatype,

  column_3 datatype

);

 

DELETE

In SQL programming, DELETE statement helps to remove rows from a table in the database. The syntax is:-

 

DELETE FROM table_name

WHERE some_column = some_value;

 

GROUP BY

GROUP BY clause helps to arrange identical data from the table into groups. In SQL, it is only used with aggregate functions like COUNT() etc. The syntax is:-

 

SELECT column_name, COUNT(*)

FROM table_name

GROUP BY column_name;

 

HAVING

HAVING is used in aggregate functions because WHERE clause cannot be used in aggregate functions. The syntax is:-

 

SELECT column_name, COUNT(*)

FROM table_name

GROUP BY column_name

HAVING COUNT(*) > value;

 

INNER JOIN

INNER JOIN helps in combining rows from different tables. Join condition must be true for the combination of rows from tables. The syntax is:-

 

SELECT column_name

FROM table_1

JOIN table_2

ON table_1.column_name = table_2.column_name;

 

OUTER JOIN

OUTER JOIN also helps to combine rows from different tables. The difference between inner join and outer join is – outer join combine rows even if the join condition is not true. Here, this condition is false, then NULL values help to fill the columns. The syntax is:-

 

SELECT column_name

FROM table_1

LEFT JOIN table_2

ON table_1.column_name = table_2.column_name;

 

INSERT

As the name predicts, INSERT statements helps to add or insert a new row to a table in the database. The syntax is:-

 

INSERT INTO table_name (column_1, column_2, column_3)

VALUES (value_1, 'value_2', value_3);

 

IS NULL / IS NOT NULL

In SQL programming, we can check empty values with the help of IS NULL / IS NOT NULL operators. The syntax is:-

 

SELECT column_name

FROM table_name

WHERE column_name IS NULL;

 

LIKE

In SQL programming, we can search for a specific pattern in a column from a table in a database with the help of LIKE operator. The syntax is:-

 

SELECT column_name

FROM table_name

WHERE column_name LIKE pattern;

 

LIMIT

LIMIT is a clause that sets a limit to the maximum number of rows the output will have. The syntax is:-

 

SELECT column_name

FROM table_name

LIMIT number;

 

MAX()

As the name suggests, MAX() function finds and returns the largest value in the column. The syntax is:-

 

SELECT MAX(column_name)

FROM table_name;

 

MIN()

As the name suggests, MIN() function finds and returns the smallest value in the column. The syntax is:-

 

SELECT MIN(column_name)

FROM table_name;

 

ORDER BY

It is very necessary to sort a table. Usually, a table is sorted with the help of a column which contains distinct values like roll number in a class of students. Use ORDER BY statement for sorting the table. Thus, this statement with a column_name defines how to sort the table. The column can be alphabetical or numerical and you have to specify the order of sorting – Ascending or Descending. The syntax is:-

 

SELECT column_name

FROM table_name

ORDER BY column_name ASC | DESC;

 

ROUND()

ROUND() is a function in SQL which rounds the values in the column to the number of decimal places specified by the given integer. The syntax is:-

 

SELECT ROUND(column_name, integer)

FROM table_name;

 

SUM()

SUM() is a function which returns the sum of all the values in the column from the table. The syntax is:-

 

SELECT SUM(column_name)

FROM table_name;

 

UPDATE

UPDATE statement help in updating the rows in a table. The syntax is:-

 

UPDATE table_name

SET some_column = some_value

WHERE some_column = some_value;

 

WITH

You can store the result set of a query in a temporary table with the help of WITH clause. The temporary table is the alias of the table and you can create as many temporary tables you want. The syntax is:-

 

WITH temporary_name AS 

(

   SELECT *

   FROM table_name

)

SELECT *

FROM temporary_name

WHERE column_name operator value;

 

We hope that you liked this post on SQL programming tutorial. For receiving posts directly to your emails, spend a minute to subscribe to our blog. Also, Don’t forget to LIKE, SHARE and COMMENT below.