Analytics Vidhya
Published in

Analytics Vidhya

SQL Queries every beginner should know

Structured Query Language or SQL (pronounced “ess-que-el”) is language which every person involved in technology have come across at some point in their life. This post gives the idea of the queries that every beginner should start with!

Image source: Google
Photo by Scott Graham on Unsplash

1. Querying data from a table:

a) Query data in columns column_1, column_2 from a table

> SELECT column_1,column_2 FROM tablename;

b) Query all rows and columns from a table

> SELECT * FROM tablename;

c) Query data and filter rows with a condition

>SELECT column_1, column_2 FROM tablename WHERE condition;

d) Query distinct rows from a table

> SELECT DISTINCT column_1 FROM tablename WHERE condition;

e) Sort the result set in ascending or descending order

> SELECT column_1,column_2 FROM tablename ORDER BY column_1 ASC [DESC];

f) Skip offset of rows and return the next n rows

> SELECT column_1,column_2 FROM tablename ORDER BY column_1 LIMIT n OFFSET offset;

g) Group rows using an aggregate function

> SELECT column_1, aggregate(column_2) FROM tablename GROUP BY column_1;

h) Filter groups using “HAVING” clause

> SELECT column_1, aggregate(column_2) FROM tablename GROUP BY column_1 HAVING condition;

Photo by Shahadat Rahman on Unsplash

2) SQL Aggregate Functions:

a) AVG: Returns the average of the list

b)COUNT: Returns number of elements in a list

c) SUM: Returns the total sum of the list

d) MAX: Returns maximum value in the list

e) MIN: Returns the minimum value in the list

Photo by Roman Kraft on Unsplash

3) Querying from Multiple Tables:

a) Inner Join in two tables table_1 and table_2

> SELECT column_1, column_2 FROM table_1 INNER JOIN table_2 ON condition;

b) Left Join in two tables table_1 and table_2

> SELECT column_1,column_2 FROM table_1 LEFT JOIN table_2 ON condition;

c) Right Join in two tables table_1 and table_2

> SELECT column_1, column_2 FROM table_1 RIGHT JOIN table_2 ON condition;

d) Full Outer Join in two tables table_1 and table_2

> SELECT column_1, column_2 FROM table_1 FULL OUTER JOIN table_2 ON condition;

e) Cartesian Product of rows in two tables table_1 and table_2

> SELECT column_1, column_2 FROM table_1 CROSS JOIN table_2;

Photo by Priscilla Du Preez on Unsplash

4) Managing Tables:

a) Create a table with three columns

> CREATE TABLE tablename (id INT PRIMARY KEY, name VARCHAR NOT NULL, price INT DEFAULT 0);

b) Delete the table from the database

>DROP TABLE tablename;

c) Add a new column to the table

> ALTER TABLE tablename ADD column;

d) Drop a column from the table

> ALTER TABLE tablename DROP COLUMN columnname;

e) Add a constraint

> ALTER TABLE tablename ADD constraint;

f) Drop a constraint

> ALTER TABLE tablename DROP constraint;

g) Rename a table

> ALTER TABLE tablename RENAME TO newtablename;

h) Rename a column

> ALTER TABLE tablename RENAME columnname TO newcolumnname;

i) Remove all data from the table

> TRUNCATE TABLE tablename;

Photo by Markus Spiske on Unsplash

5)Modifying Data:

a) Insert a row into a table

> INSERT INTO tablename(column_list) VALUES(values_list);

b) Insert multiple rows into a table

> INSERT INTO tablename(column_list) VALUES(value_list),(value_list),….;

c) Insert rows from table_2 to table_1

> INSERT INTO table_1(column_list) SELECT column_list FROM table_2;

d) Delete subset of rows in a table

> DELETE FROM tablename WHERE condition;

e) Delete all data in a table

> DELETE FROM tablename;

f) Update new value in the column_1 for all rows

> UPDATE tablename SET column_1=new_value;

g) Update values in the columns column_1, column_2 that match the condition

> UPDATE tablename SET column_1=new_value, column_2=new_
value WHERE condition;

For queries, feel free to write in the comment💬 section below. You can connect with me on LinkedIn !!

Thank you for reading! Have a great day ahead😃

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store