SQL Basics to get you started

SQL stands for Structured Query Language and it is used to pull relevant data from the database. We assume that you already know a bit about SQL, so we will try to cover the base and then will quickly jump to some questions.

1. What all can we do with SQL?

We can perform the following task:-
a. Retrieval of data
b. Updation of data
c. Insertion of data
d. Deletion of data

2. A table is basically a set of data which is organized in the form of rows and columns.

3. What are the different subsets of SQL?
We can further divide SQL into 3 parts or subsets:-
a. Data Definition Language – It helps you to CREATE, ALTER, and DELETE objects
b. Data Manipulation Language –It helps us to insert, update, delete, and retrieve data from the database
c. Data Control Language – DCL allows you to control access to the database. Grant and Revoke access permission

4. What is a Primary Key?
– A key which uniquely defines each row in a table and it cannot be NULL. Example – Roll number or Employee ID in a student or Employee table

5. What are constraints?
– Constraints are used to apply a limit on the data type of the table. Following are a few examples of constraints:-
a. Primary Key
b. Foreign Key
c. Unique
d. Default
f. Check
g. Not Null

6. What is a unique key?
– A key which uniquely defines each row in a table. It is mostly a group of keys which can or can not be NULL

7. What are the different types of Joins?
– There are various types of Joins, the most important out of these are:-
a. Inner Join – Intersection of two tables
b. Right Join – All the values from the right table and their corresponding matched value from the left table
c. Left Join – All the values from the left table and their corresponding matched values from the right table
d. Outer Join – All the values from both the table

8. We will not be talking about ACID properties and Normalization. These are very easily available on the internet. It would be very good if you watch a couple of videos on these topics on Youtube. The links of the videos are given below:-

Normalization
ACID Properties

9. What is a NULL value?
A NULL value is neither zero nor a blank space. It represents a value that it unavailable, unknown, Not applicable, etc.
Remember zero is a number and blank space is a character

10. What is the order of SQL query execution?

11. What is the difference between DROP and TRUNCATE commands?
-DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.

12. What is a view in SQL?
– Views in SQL are kind of virtual tables. A view also has rows and columns as they are
in a real table in the database. We can create a view by selecting fields from one or
more tables present in the database. A View can either have all the rows of a table or
specific rows based on certain condition

13. What is Union, minus and Interact commands?
UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.

14. Important syntax of SQL below:-

a. BETWEEN
SELECT *
FROM Table Name
WHERE Date BETWEEN ‘date1’ AND ‘date2’

b. ORDER BY
SELECT *
FROM Table Name
WHERE Condition
ORDER BY {ASC/DESC}

c. HAVING
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

d. TRUNCATE Table
TRUNCATE TABLE Table_Name

e. ALTER Table
ALTER TABLE Table_Name {ADD/DROP/MODIFY} Column_Name {Data Type}

f. INSERT INTO
INSERT INTO table_name( column1, column2….columnN)
VALUES ( val1, val2….valN);

g. UPDATE
UPDATE table_name
SET column1 = val1, column2 = val2….columnN=valN
[ WHERE CONDITION ];

15. Explain SQL clause
The SQL LIKE clause is used to compare a value to similar values using wildcard operators.
There are two wildcards used in conjunction with the LIKE operator.
The percent sign (%)
The underscore (_)
The percent sign represents zero, one or multiple characters. The underscore represents a single number or character.
These symbols can be used in combinations.

16. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
HAVING clause can be used only with SELECT statement.
It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query
whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.

17. What is the difference between JOIN and UNION?
-UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. By using JOINs, you can retrieve data from two or more tables based on logical relationships between the tables
JOIN:-
a.) Joins the columns
b.)Duplicates are allowed
c.) Combines the column based on condition

UNION:-
a.) Merge the row
b.) Duplicates are not allowed
c.) Combine the result of two select statements.

18. What is self join?
– The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

19.
SELECT – select records from a table
INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records
CREATE – create a new Table, database, schema
ALTER – alter existing table, column description
DROP – delete existing objects from database
GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects

20. What is the difference between inner and outer join? Explain with example.
– Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).Inner join returns rows when there is at least one match in both tables.
If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

Outer Join- Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).Outer Join can be full outer or single outer.

These were the very basic questions and topics. Apart from it there are some interesting questions waiting for you in the next exercise where we will deal with partition by, ranking and some tricky SQL questions.

You can easily find the explanation of the above questions on the internet, else you can also look into our book on SQL

112 Questions to crack Data Science Interview using SQL

There are few more questions on TheDataMonk which you can find here

Keep Practicing !!

XtraMous