To understand SQL, we must first understand databases and database management systems (DBMS).
Data is essentially a collection of facts about a specific object. A database is a collection of small units of data that are organised in a logical manner. A Relational Database Management System (RDBMS) is a set of tools that allows users to manage, organise, and visualise the contents of a database while adhering to some common criteria that allow for fast response between the database and the user side.
After learning about data, databases, and DBMS/RDBMS, we can finally learn about SQL. SQL, or Structured Query Language, is the language that we (the users) use to communicate with databases and extract the information we need. It is used to store, manipulate, and retrieve data from a database.
SQL allows us to interact with databases and extract/manipulate data from them. We can use SQL to create our own databases and then populate them with data in the form of tables.
DDL (Data Definition Language) COMMANDS:
1.CREATE to create a new table or database.
2.ALTER for alteration.
3.Truncate to delete data from the table.
4.DROP to drop a table.
5. RENAME to rename a table.
DML (Data Manipulation Language) COMMANDS:
1. INSERT : It is used to insert data into a table.
2. UPDATE: It is used to update existing data within a table.
3. DELETE : It is used to delete records from a database table.
4. LOCK: Table control concurrency.
5. CALL: Call a PL/SQL or JAVA subprogram.
6. EXPLAIN PLAN: It describes the access path to data.
DQL (Data Query Language) COMMANDS:
AND OR NOT: Logical Operators.
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
ARITHMETIC OPERATORS: +, -, *, /, %
For example, if we want to return new salary of employees from employee table
by adding 10% of the previous salary:
STRING OPERATIONS:
WILDCARDS: Used for pattern matching along with LIKE operators.
ALIASES: Gives temporary names to columns and tables. Used with AS operator.
ORDER BY: Used to sort columns.
SET OPERATIONS:
AGGREGATE FUNCTIONS: MIN, MAX, SUM, AVG, COUNT
a. COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
b. SUM function: Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
c. AVG function: The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.
d. MAX function: MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
e. MIN function: MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
GROUP BY: Used to arrange distinct data into groups. Often used with aggregate functions. Can be followed by ORDER BY to sort the groups formed.
For example, we want to find the average salary of employees from each department:
HAVING: Used with GROUP BY to check for conditions.
For example, we want only those departments from the previous example where salary is greater the 40000:
NESTED SUBQUERY:
A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, SOME, BETWEEN, etc.
JOINS: Combines rows from two or more tables, based on a related column between them.
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all reco36rds from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
This blog gives you a rough pathway of core questions ans topics to follow. Ofcourse, you can and should explore more on your journey. There will be new blogs coming on SQL interview preparation soon, so stay tuned. Also, if you have any particular topic that you want covered, please write to us. If you wanna connect with me, here is my linkedin profile — Sneha Biswas. If you have any questions, feel free to contact me.
WHAT IS WEB3?Web3 is the catch-all term for the vision of an upgraded version of the internet aimed at giving power back to the users. It is truly the......
Operating Systems is one of the core subjects for a computer science student. As such a lot of important questions are asked on this subject in interv......
Getting started with cloud computing ?The words "cloud" and "web" will be interchangeable. What kind of business operates today without the internet? ...
SOFTWARE MAINTENANCE:Software Maintenance can be defined as the process of modifying a software product after its delivery. The main purpose of softwa...
A quick summary upto what we did this far from this series:Software EngineeringSoftware engineering is a discipline of engineering concerned wit...
Introduction:Understanding time complexity of a function is imperative for every programmer to execute a program in the most efficient manner. Time co...
Software MethodologyA software methodology (which is also known as software process) is a set of related development Phases that leads to the pr...
Software Engineering is a subject that requires when you are a software developer as well as when you are in your college preparing for a semester or ...
Are you getting ready for your SQL developer job interview?You've come to the correct place.This tutorial will assist you in brushing up on your SQL s...
Networking: Importance in real life!Using a computer for work, as well as for personal use, ha...
FORMULA LIST:ALGEBRA :1.Sum of first n natural numbers = n(n+1)/22.Sum of the squares of first n nat...
ER-DiagramWhat are ER Models ?An Entity Relationship Diagram (ERD) is a visual representation of dif...
What is actually DBMS?A Database Management System (DBMS) is system software used to manage the orga...
C CHEATSHEETINTRODUCTION:C programming language was developed by Dennis Ritchie of Bell Labs in 1972...
C is a general purpose high-level language most popular amongst coders, it is the most compatible, e...
C++ programming language has templates which basically allows functions and classes to work with gen...
C++ was conceived in the late 1970s to overcome the limitations of C. It is an extension of th...
Interview Questions are a great source to test your knowledge about any specific field. But remember...
The most popular high-level, multipurpose programming language right now is Python.Python supports p...
IntroductionPython is a high-level programming language with dynamic binding and high-level inherent...
Interview Questions are a great source to test your knowledge about any specific field. But remember...
Object-Oriented Programming or better known as OOPs is one of the major pillars of Java that has lev...
Java is a high-level programming language known for its robustness, object-oriented nature, enhanced...