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!
Structured Query Language or SQL is a domain-specific language used for designing, programming and communicating with data held in the Relational Database Management System (RDBMS)
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;
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
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;
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;
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😃
