top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

SQL(Structure Query Language)

SQL is a standard language for storing, manipulating and retrieving data in databases. My SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems. SQL stands for Structured Query Language designed to manipulate data in the Relational Database Management Systems (RDBMS).

SQL consists of a data definition language, data manipulation language, and a data control language.

  • The data definition language deals with the schema creation and modification e.g., CREATE TABLE statement allows you to create a new table in the database and the ALTER TABLE statement changes the structure of an existing table.

  • The data manipulation language provides the constructs to query data such as the SELECT statement and to update the data such as INSERT, UPDATE, and DELETE statements.

  • The data control language consists of the statements that deal with the user authorization and security such as GRANT and REVOKE statements.


How to Download SQL in system:

  1. first, go to the google and download the sql from https://www.mysql.com/.

  2. After downloading the SQL start installation.

Then, you will get MySQL Workbench. Here you go, you can start yours Queries, Database ,Table and many more.




Down is the SQL syntax that helps you understand the details of SQL statements:




1. SQL SELECT – selecting data from specific columns:

You can specify the column list after the SELECT clause of the SELECT statement.

For example, the following select data from the employee id, first name, last name, and hire date of all rows in the employees table:




Now, the result set includes only four columns specified in the SELECT clause:





2. Introduction to SQL ORDER BY clause:


The ORDER BY is an optional clause of the SELECT statement. The ORDER BY clause allows you to sort the rows returned by the SELECT clause by one or more sort expressions in ascending or descending order.

The following shows the syntax of the ORDER BY clause:



3.Introduction to SQL DISTINCT operator:


If you use two or more columns, the DISTINCT will use the combination of values in those columns to evaluate the duplicate. Moreover, the DISTINCT only removes the duplicate rows from the result set. It doesn’t delete duplicate rows in the table.



4. Introduction to SQL WHERE clause:

The WHERE clause appears immediately after the FROM clause. The WHERE clause contains one or more logical expressions that evaluate each row in the table. If a row that causes the condition evaluates to true, it will be included in the result set; otherwise, it will be excluded. The following illustrates the syntax of the WHERE clause in the SELECT statement:



5.Introduction to the SQL INNER JOIN clause:

The process of linking tables is called joining. SQL provides many kinds of joins such as inner join, left join, right join, full outer join, etc. This tutorial focuses on the inner join.

Suppose, you have two tables: A and B.

Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)

When table A joins with table B using the inner join, you have the result set (3,4) that is the intersection of table A and table B.

See the following picture as well as the following statement illustrates how to join 3 tables: A, B, and C:





Same like, we have Left join, Right join, Self join and full Join.



6. SQL Aggregate Functions:

An SQL aggregate function calculates on a set of values and returns a single value. For example, the average function ( AVG) takes a list of values and returns the average.

Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the result set into groups of values and the aggregate function returns a single value for each group.

The following are the commonly used SQL aggregate functions:

  •  AVG() – returns the average of a set.

  •  COUNT() – returns the number of items in a set.

  •  MAX() – returns the maximum value in a set.

  •  MIN() – returns the minimum value in a set

  •  SUM() – returns the sum of all or distinct values in a set


7.Introduction to SQL GROUP BY clause:


The GROUP BY is an optional clause of the SELECT statement. The GROUP BY clause allows you to group rows based on values of one or more columns. It returns one row for each group.

Syntax:

SELECT column1, column2, aggregate_function(column3) FROM table_name GROUP BY column1, column2;

We have HAVING CLAUSE, GROUPING SETTING ROLL-UP, CUBE too in Grouping Data like GROUP BY clause.



8.Introduction to the SQL INSERT statement:

SQL provides the INSERT statement that allows you to insert one or more rows into a table




The other commands are Update and Delete.

In update :-  update existing data in a table.

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


In Delete:- delete data from a table permanently.

DELETE FROM table_name WHERE condition;


9.Working with table structures:-

a) create a Table: create a new table in the database.

CREATE TABLE courses ( course_id INT AUTO_INCREMENT PRIMARY KEY, course_name VARCHAR(50) NOT NULL );

b)  Alter Table:  modify the structure of an existing table.

ALTER TABLE courses ADD credit_hours INT NOT NULL;

C)   Drop Table:-   remove the tables permanently.





d) Turncate Table:- delete all data in a big table fast and efficiently.

TRUNCATE TABLE table_name;


10.Constraints:-

A table consists of columns and rows. Typically, a table has a column or set of columns whose values uniquely identify each row in the table. This column or the set of columns is called the primary key.

The primary key that consists of two or more columns is also known as the composite primary key.

Each table has one and only one primary key. The primary key does not accept NULL or duplicate values.



11.Subquery:-

  • Subquery – show you how to nest a query inside another query to form a more flexible query for querying data.

  • Correlated Subquery – introduce you to the correlated subquery which is a subquery that uses values from the outer query.

  • EXISTS – show you how to check for the existence of the row returned from a subquery.

  • ALL – illustrate how to query data by comparing values in a column of the table with a set of columns.

  •  ANY – query data if a value in a column of a table matches one of the values in a set.


Last But not least, SET Operators:-

The UNION operator combines result sets of two or more SELECT statements into a single result set. The following statement illustrates how to use the UNION operator to combine result sets of two queries:

SELECT 
    column1, column2
FROM
    table1 
UNION [ALL]
SELECT 
    column3, column4
FROM
    table2;


There are three types of Set operators:-

  • UNION and UNION ALL – combine result sets of two or more queries into a single result set using the UNION and UNION ALL operators.

  • INTERSECT  – return the intersection of two or more queries using the INTERSECT operator.

  • MINUS – subtract a result set from another result set using the MINUS operator.


Reference upon:-


Thanks for Reading...!!


108 views0 comments

Recent Posts

See All

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page