Crash Course on SQL

SQL   is a Structured Query Language which can be stored, modified, and retrieved using SQL which is a standard language.

Advantages of SQL

SQL has the ability to run queries against databases. It has the ability to input, update, delete, and generate data into databases.

Know About RDBMS

RDBMS is a Relational DataBase Management System.Tables are database objects used to hold data in RDBMS. A table is made up of rows and columns and contains entries for linked data.

Basic Query :SELECT *FROM TABLE

SQL Command Types

SQL commands come in five different subcategories: DDL, DML, DCL, TCL, and DQL.

1.Data Definition Language (DDL)

DDL modifies the structure of the table by adding, removing, or changing tables, among other things.
All DDL commands are automatically committed, which permanently saves all database modifications.

2.Data Manipulation Language

The database can be changed by using DML commands. It is in charge of making any kind of database modifications.
DML commands cannot permanently save all database changes since they are not auto-committed. They are rollbackable.

3.Data Control Language

Any database user’s authority can be granted and revoked with the help of DCL commands.

4.Transaction Control Language

Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands.

These operations cannot be utilised while creating or deleting tables since they are automatically committed in the database.

5.Data Query Language

The data is fetched from the database using DQL.

 

BASIC COMMANDS

1.SELECT Syntax

To choose data from a database, use the SELECT statement.

SELECT column 1..*FROM tablename;

Here choose Table name which you want to fetch data from the table and if you want to fetch records from some specific column then write there column name.

If you want to fetch all the records of a specific table then use below query

SELECT * FROM table_name;

2.SELECT DISTINCT Statement in SQL

To only return distinct (different) data, use the SELECT DISTINCT command.

SELECT DISTINCT column1, column2, …FROM table_name;

3.WHERE clause in SQL

If you want to filter some records from the table then you can add this condition “WHERE”

SELECT column1 …FROM table_name WHERE condition;

4.AND, OR AND NOT CONDITIONS IN SQL COMMAND

If every condition that is divided by AND is TRUE, the AND operator displays a record and If any of the terms divided by OR is TRUE, the OR operator outputs a record.

If the condition(s) is/are NOT TRUE, the NOT operator displays a record.

A.AND SYNTAX

SELECT column1, column2, …FROM table_name WHERE condition1 AND condition2 AND condition3 …;

B.OR SYNTAX

SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;

C.NOT SYNTAX

SELECT column1, column2, …FROM table_name WHERE NOT condition;

5.ORDER BY KEYWORD IN SQL

The result set can be sorted in either ascending or descending order using the ORDER BY keyword.

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

6.INSERT INTO Statement in SQL

To add new records to a table, use the INSERT INTO statement.

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

7.NULL values in SQL

A field with a NULL value has no value at all.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

8.UPDATE QUERY in SQL

If youwant to Update records in a table then use the UPDATE Query.Once you Update the records in a table  can be changed using the UPDATE command.

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

Read More:SQL

 

9.DELETE QUERY in SQL

If you want to delete records from the table then use delete query to delete the records from the table using delete query.

DELETE FROM table_name WHERE condition;

10.MIN AND MAX QUERY IN SQL COMMAND

If you want to find the MIN Value the use the MIN Query and if you want to find the Max value then use the MAX Query.

SELECT MIN(column_name) FROM table_name WHERE condition;

SELECT MAX(column_name) FROM table_name WHERE condition;

11.COUNT(),AVG() AND SUM() Functions

COUNT() returns the number of function , AVG() returns the average of the records and if you want to find the total sum of the records then use the SUM()

SELECT COUNT(column_name) FROM table_name WHERE condition;

SELECT AVG(column_name) FROM table_name WHERE condition;

SELECT SUM(column_name) FROM table_name WHERE condition;

12.JOIN QUERY IN SQL

If you want to join the records of two or more than two records the use JOIN Query.We have several JOIN Query like INNER JOIN, OUTER JOIN ,LEFT INNER JOIN AND RIGHT INNER JOIN.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

1.INNER JOIN-It returns entries with values that are same across both tables.

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

2.LEFT JOIN-It  return that includes both the matched entries from the right table and all of the records from the left table.

SELECT column_name(s) FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;

3.RIGHT JOIN-IT brings up the matching records from the left table and all of the records from the right table.

SELECT column_name(s) FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

4.FULL  JOIN-When there is a match in either the left or right table, it returns all records.

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

 

 

Statement:

1.SQL CREATE DATABASE

If you want to create Database then use CREATE Statement

CREATE DATABASE databasename;

2.CREATE TABLE STATEMENT

If you want to create table in a database then use CRETE TABLE STATEMENT

CREATE TABLE table_name ;

3.SQL UNIQUE STATEMENT

All of the values in a column must be unique, thanks to the UNIQUE constraint.

CREATE TABLE Persons ( ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int);

4.PRIMARY KEY STATEMENT IN SQL

Each record in a table is given a special identification by the PRIMARY KEY constraint.

Primary keys cannot have NULL values and must have UNIQUE values.

There can only be one primary key in a table, and this primary key may include one or more columns (fields).

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL,
    FirstName varchar(255), Age int,PRIMARY KEY (ID));

5.FOREIGN KEY CONSTRAINT

In order to avoid operations that would break links between tables, the FOREIGN KEY constraint is employed.

CREATE TABLE Orders (OrderID int NOT NULL,OrderNumber int NOT NULL,PersonID int, PRIMARY KEY (OrderID),FOREIGN KEY (PersonID) REFERENCES Persons(PersonID));

 

KNOW ABOUT:CRASH COURSE OF GIT