LogoLogo

SQL CHEATSHEET

Prasun Das| July 12, 2022 at 9:09 PM | 4 minutes Read

Introduction: What is SQL?


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 Features


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:



  1. SELECT STATEMENT: 




  1. DISTINCT: Returns the identical rows.




  1. WHERE: Used for giving constraints.



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:



  1. The AND operator displays a record if all the conditions separated by AND are TRUE.


  1. The OR operator displays a record if any of the conditions separated by OR is TRUE.


  1. The NOT operator displays a record if the condition(s) is NOT TRUE.




  1. BETWEEN:




  1. NOT BETWEEN:



  1. IN: It allows you to assign multiple values to the WHERE Clause. An alternative to multiple OR operators.





  1. Can also be used with NOT Operators.



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.



  1. % (for zero or more characters):




  1. _ (for single character):



ALIASES: Gives temporary names to columns and tables. Used with AS operator.


  1. Column: 




  1. Table: 



ORDER BY: Used to sort columns.



  1. Ascending (default):




  1. Descending:




SET OPERATIONS:




  1. UNION: Used to combine the result from two SELECT statements without duplicates.




  1. UNION ALL: Used to combine the result from two SELECT statements with duplicates.




  1. INTERSECTION: Returns only common rows returned by the two SELECT statements




  1. EXCEPT: Returns only those rows that are not returned by the second SELECT statement.




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.


#dbms#sql#cheatsheet#last minute notes#computer fundamentals#fundamentals to learn
View Count:3.8k
12

Comments

Similar Articles

Busting myths about web3

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......

Cover image

Operating System INTERVIEW QUESTIONS

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......

Cover image

Cloud Computing Cheatsheet

Getting started with cloud computing ?The words "cloud" and "web" will be interchangeable. What kind of business operates today without the internet? ...

Cover image

Software Engineering — SOFTWARE MAINTENANCE [Part-4]

SOFTWARE MAINTENANCE:Software Maintenance can be defined as the process of modifying a software product after its delivery. The main purpose of softwa...

Cover image

Software Engineering — Software Process Activities [Part - 3]

A quick summary upto what we did this far&nbsp; from this series:Software EngineeringSoftware engineering is a discipline of engineering concerned wit...

Cover image

Understanding Time Complexity with Interview Examples

Introduction:Understanding time complexity of a function is imperative for every programmer to execute a program in the most efficient manner. Time co...

Cover image

Software Engineering — Software Development lifecycle and software process(Part 2)

&nbsp;Software MethodologyA software methodology (which is also known as software process) is a set of related development Phases that leads to the pr...

Cover image

Software Engineering [Part 1] - Introduction

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 ...

Cover image

SQL Interview Questions

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...

Cover image

Computer Networking Cheatsheet (Last Minute Notes)

&nbsp;Networking: Importance in real life!Using a computer for work, as well as for personal use, ha...

Cover image

Last Minute Preparation Cheat Sheet for Quantitative Aptitude

FORMULA LIST:ALGEBRA :1.Sum of first n natural numbers = n(n+1)/22.Sum of the squares of first n nat...

Cover image

Database Management System Last Minute Notes [Part - 2]

ER-DiagramWhat are ER Models ?An Entity Relationship Diagram (ERD) is a visual representation of dif...

Cover image

Database Management System Last Minute Notes [Part - 1]

What is actually DBMS?A Database Management System (DBMS) is system software used to manage the orga...

Cover image

C Cheatsheet

C CHEATSHEETINTRODUCTION:C programming language was developed by Dennis Ritchie of Bell Labs in 1972...

Cover image

C Interview Questions — 2022

C is a general purpose high-level language most popular amongst coders, it is the most compatible, e...

Cover image

C++ STL Cheat Sheet

C++ programming language has templates which basically allows functions and classes to work with gen...

Cover image

C++ CHEAT SHEET

C++ was conceived in the late 1970s to overcome the limitations of C. It&nbsp; is an extension of th...

Cover image

Python [Advanced] Interview Questions — 2022

Interview Questions are a great source to test your knowledge about any specific field. But remember...

Cover image

Basic Python [Core] Interview Questions for Freshers and Short Sample Answers — 2022

The most popular high-level, multipurpose programming language right now is Python.Python supports p...

Cover image

Python Cheat Sheet - Learn the basics of Python

IntroductionPython is a high-level programming language with dynamic binding and high-level inherent...

Cover image

Basic Java Interview Questions for Freshers and Short Sample Answers — 2022

Interview Questions are a great source to test your knowledge about any specific field. But remember...

Cover image

Java OOP Cheat Sheet — A Quick Guide to Object-Oriented Programming in Java

Object-Oriented Programming or better known as OOPs is one of the major pillars of Java that has lev...

Cover image

Learn Java: Basics to Advanced Concepts [Java Cheatsheet]

Java is a high-level programming language known for its robustness, object-oriented nature, enhanced...

Cover image