Scribd
Upload
15 views8 pages

Beginner's Guide to SQL Tutorial

This SQL tutorial is designed for beginners to understand both basic and advanced concepts of SQL, which is a language for managing and retrieving data in relational databases. It assumes prior knowledge of databases and programming languages. The tutorial includes various topics, commands, and examples related to SQL, along with copyright and disclaimer notices.

Uploaded by

tarek hassan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views8 pages

Beginner's Guide to SQL Tutorial

This SQL tutorial is designed for beginners to understand both basic and advanced concepts of SQL, which is a language for managing and retrieving data in relational databases. It assumes prior knowledge of databases and programming languages. The tutorial includes various topics, commands, and examples related to SQL, along with copyright and disclaimer notices.

Uploaded by

tarek hassan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

ABOUT THE TUTORIAL

SQL Tutorial
SQL is a database computer language designed for the retrieval and management of data in relational
database. SQL stands for Structured Query Language.

This tutorial will give you quick start with SQL.

Audience
This reference has been prepared for the beginners to help them understand the basic to advanced
concepts related to SQL languages.

Prerequisites
Before you start doing practice with various types of examples given in this reference, I'm making an
assumption that you are already aware about what is database, especially RDBMS and what is a
computer programming language.

Copyright & Disclaimer Notice


All the content and graphics on this tutorial are the property of [Link]. Any content from
[Link] or this tutorial may not be redistributed or reproduced in any way, shape, or form
without the written permission of [Link]. Failure to do so is a violation of copyright laws.

This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the
accuracy of the site or its contents including this tutorial. If you discover that the [Link] site
or this tutorial content contains some errors, please contact us at webmaster@[Link]

TUTORIALS POINT
Simply Easy Learning
Table of Content
SQL Tutorial ............................................................................. 2
Audience .................................................................................. 2
Prerequisites ............................................................................ 2
Copyright & Disclaimer Notice.................................................. 2
SQL Overview ........................................................................ 15
What is SQL? ........................................................................................... 15
Why SQL? ................................................................................................ 15
History: ..................................................................................................... 16
SQL Process: ........................................................................................... 16
SQL Commands: ...................................................................................... 17
DDL - Data Definition Language: ............................................................. 17
DML - Data Manipulation Language: ........................................................ 17
DCL - Data Control Language: ................................................................. 17
DQL - Data Query Language: .................................................................. 17
SQL RDBMS Concepts .......................................................... 18
What is field?............................................................................................ 19
What is record, or row? ............................................................................ 19
What is column? ....................................................................................... 19
What is NULL value?................................................................................ 19
SQL Constraints: ...................................................................................... 20
NOT NULL Constraint: ............................................................................. 20
DEFAULT Constraint:............................................................................... 21
Example: .................................................................................................. 21
Drop Default Constraint: ........................................................................... 21
UNIQUE Constraint: ................................................................................. 21
Example: .................................................................................................. 21
DROP a UNIQUE Constraint:................................................................... 22
PRIMARY Key: ......................................................................................... 22
Create Primary Key: ................................................................................. 22
Delete Primary Key: ................................................................................. 23
FOREIGN Key: ......................................................................................... 24
Example: .................................................................................................. 24
DROP a FOREIGN KEY Constraint: ........................................................ 25
CHECK Constraint: .................................................................................. 25
Example: .................................................................................................. 25
DROP a CHECK Constraint: .................................................................... 25
INDEX: ..................................................................................................... 26

TUTORIALS POINT
Simply Easy Learning
Example: .................................................................................................. 26
DROP a INDEX Constraint:...................................................................... 26
Data Integrity: ........................................................................................... 26
Database Normalization ........................................................................... 27
Third Rule of 1NF: .................................................................................... 29
SQL RDBMS Databases ........................................................ 32
MySQL ..................................................................................................... 32
History: ..................................................................................................... 32
Features: .................................................................................................. 33
MS SQL Server ........................................................................................ 33
History: ..................................................................................................... 33
Features: .................................................................................................. 34
ORACLE .................................................................................................. 34
History: ..................................................................................................... 34
Features: .................................................................................................. 35
MS- ACCESS ........................................................................................... 36
Features: .................................................................................................. 36
SQL Syntax ............................................................................ 37
SQL SELECT Statement: ......................................................................... 37
SQL DISTINCT Clause: ........................................................................... 37
SQL WHERE Clause:............................................................................... 37
SQL AND/OR Clause: .............................................................................. 37
SQL IN Clause: ........................................................................................ 38
SQL BETWEEN Clause: .......................................................................... 38
SQL Like Clause: ..................................................................................... 38
SQL ORDER BY Clause: ......................................................................... 38
SQL GROUP BY Clause: ......................................................................... 38
SQL COUNT Clause: ............................................................................... 38
SQL HAVING Clause: .............................................................................. 38
SQL CREATE TABLE Statement: ............................................................ 38
SQL DROP TABLE Statement: ................................................................ 39
SQL CREATE INDEX Statement: ............................................................ 39
SQL DROP INDEX Statement: ................................................................ 39
SQL DESC Statement: ............................................................................. 39
SQL TRUNCATE TABLE Statement: ....................................................... 39
SQL ALTER TABLE Statement: ............................................................... 39
SQL ALTER TABLE Statement (Rename): .............................................. 39
SQL INSERT INTO Statement: ................................................................ 39
SQL UPDATE Statement: ........................................................................ 39

TUTORIALS POINT
Simply Easy Learning
SQL DELETE Statement: ......................................................................... 40
SQL CREATE DATABASE Statement: .................................................... 40
SQL DROP DATABASE Statement: ........................................................ 40
SQL USE Statement: ............................................................................... 40
SQL COMMIT Statement: ........................................................................ 40
SQL ROLLBACK Statement:.................................................................... 40
SQL Data Types .................................................................... 41
Exact Numeric Data Types: ...................................................................... 41
Approximate Numeric Data Types: .......................................................... 41
Date and Time Data Types: ..................................................................... 42
Character Strings Data Types: ................................................................. 42
Unicode Character Strings Data Types: ................................................... 42
Binary Data Types: ................................................................................... 42
Misc Data Types: ..................................................................................... 43
SQL Operators ....................................................................... 44
What is an Operator in SQL? ................................................................... 44
SQL Arithmetic Operators: ....................................................................... 44
SQL Comparison Operators: .................................................................... 45
SQL Logical Operators: ............................................................................ 47
SQL Expressions ................................................................... 50
Syntax: ..................................................................................................... 50
SQL - Boolean Expressions: .................................................................... 50
SQL - Numeric Expression: ...................................................................... 51
SQL - Date Expressions: .......................................................................... 51
SQL CREATE Database ........................................................ 53
Syntax: ..................................................................................................... 53
Example: .................................................................................................. 53
DROP or DELETE Database ................................................. 54
Syntax: ..................................................................................................... 54
Example: .................................................................................................. 54
SQL SELECT Database......................................................... 55
Syntax: ..................................................................................................... 55
Example: .................................................................................................. 55
SQL CREATE Table .............................................................. 56
Syntax: ..................................................................................................... 56
Syntax: ..................................................................................................... 56
Example: .................................................................................................. 57
Example: .................................................................................................. 57
SQL DROP or DELETE Table................................................ 59

TUTORIALS POINT
Simply Easy Learning
Syntax: ..................................................................................................... 59
Example: .................................................................................................. 59
SQL INSERT Query ............................................................... 60
Syntax: ..................................................................................................... 60
Example: .................................................................................................. 60
Populate one table using another table: ................................................... 61
SQL SELECT Query .............................................................. 62
Syntax: ..................................................................................................... 62
Example: .................................................................................................. 62
SQL WHERE Clause ............................................................. 64
Syntax: ..................................................................................................... 64
Example: .................................................................................................. 64
SQL AND and OR Operators ................................................. 66
The AND Operator: .................................................................................. 66
Syntax: ..................................................................................................... 66
Example: .................................................................................................. 66
The OR Operator: .................................................................................... 67
Syntax: ..................................................................................................... 67
Example: .................................................................................................. 67
SQL UPDATE Query.............................................................. 69
Syntax: ..................................................................................................... 69
Example: .................................................................................................. 69
SQL DELETE Query .............................................................. 71
Syntax: ..................................................................................................... 71
Example: .................................................................................................. 71
SQL LIKE Clause ................................................................... 73
Syntax: ..................................................................................................... 73
Example: .................................................................................................. 74
SQL TOP Clause ................................................................... 75
Syntax: ..................................................................................................... 75
Example: .................................................................................................. 75
SQL ORDER BY Clause ........................................................ 77
Syntax: ..................................................................................................... 77
Example: .................................................................................................. 77
SQL Group By........................................................................ 79
Syntax: ..................................................................................................... 79
Example: .................................................................................................. 79
SQL Distinct Keyword ............................................................ 81
Syntax: ..................................................................................................... 81

TUTORIALS POINT
Simply Easy Learning
Example: .................................................................................................. 81
SQL SORTING Results.......................................................... 83
Syntax: ..................................................................................................... 83
Example: .................................................................................................. 83
SQL Constraints..................................................................... 85
NOT NULL Constraint: ............................................................................. 85
DEFAULT Constraint:............................................................................... 86
Example: .................................................................................................. 86
Drop Default Constraint: ........................................................................... 86
UNIQUE Constraint: ................................................................................. 87
Example: .................................................................................................. 87
DROP a UNIQUE Constraint:................................................................... 87
PRIMARY Key: ......................................................................................... 88
Create Primary Key: ................................................................................. 88
Delete Primary Key: ................................................................................. 89
FOREIGN Key: ......................................................................................... 89
Example: .................................................................................................. 89
DROP a FOREIGN KEY Constraint: ........................................................ 90
CHECK Constraint: .................................................................................. 90
Example: .................................................................................................. 90
DROP a CHECK Constraint: .................................................................... 91
INDEX: ..................................................................................................... 91
Example: .................................................................................................. 91
DROP a INDEX Constraint:...................................................................... 91
Dropping Constraints:............................................................................... 92
Integrity Constraints: ................................................................................ 92
SQL Joins .............................................................................. 93
SQL Join Types: ....................................................................................... 94
INNER JOIN ............................................................................................. 94
Syntax: ..................................................................................................... 94
Example: .................................................................................................. 94
LEFT JOIN ............................................................................................... 95
Syntax: ..................................................................................................... 95
Example: .................................................................................................. 95
RIGHT JOIN ............................................................................................. 96
Syntax: ..................................................................................................... 96
Example: .................................................................................................. 96
FULL JOIN ............................................................................................... 97
Syntax: ..................................................................................................... 97

TUTORIALS POINT
Simply Easy Learning
Example: .................................................................................................. 98
SELF JOIN ............................................................................................... 99
Syntax: ..................................................................................................... 99
Example: .................................................................................................. 99
CARTESIAN JOIN ................................................................................. 100
Syntax: ................................................................................................... 100
Example: ................................................................................................ 100
SQL Unions Clause ............................................................. 102
Syntax: ................................................................................................... 102
Example: ................................................................................................ 102
The UNION ALL Clause: ........................................................................ 103
Syntax: ................................................................................................... 103
Example: ................................................................................................ 104
Example: ................................................................................................ 105
SQL NULL Values ................................................................ 109
Syntax: ................................................................................................... 109
Example: ................................................................................................ 109
SQL Alias Syntax ................................................................. 111
Syntax: ................................................................................................... 111
Example: ................................................................................................ 111
SQL Indexes ........................................................................ 113
The CREATE INDEX Command: ........................................................... 113
Single-Column Indexes: ......................................................................... 113
Unique Indexes: ..................................................................................... 113
Composite Indexes: ............................................................................... 114
Implicit Indexes: ..................................................................................... 114
The DROP INDEX Command: ............................................................... 114
When should indexes be avoided? ........................................................ 114
SQL ALTER TABLE Command............................................ 115
Syntax: ................................................................................................... 115
Example: ................................................................................................ 116
SQL TRUNCATE TABLE ..................................................... 118
Syntax: ................................................................................................... 118
Example: ................................................................................................ 118
SQL - Using Views ............................................................... 119
Creating Views: ...................................................................................... 119
Example: ................................................................................................ 119
The WITH CHECK OPTION:.................................................................. 120
Updating a View: .................................................................................... 120

TUTORIALS POINT
Simply Easy Learning
Inserting Rows into a View: .................................................................... 121
Deleting Rows into a View: ..................................................................... 122
Dropping Views: ..................................................................................... 122
SQL HAVING CLAUSE ........................................................ 123
Syntax: ................................................................................................... 123
Example: ................................................................................................ 123
SQL Transactions ................................................................ 125
Properties of Transactions: .................................................................... 125
Transaction Control: ............................................................................... 125
The COMMIT Command: ....................................................................... 125
Example: ................................................................................................ 126
The ROLLBACK Command: .................................................................. 126
Example: ................................................................................................ 126
The SAVEPOINT Command: ................................................................. 127
Example: ................................................................................................ 127
The RELEASE SAVEPOINT Command:................................................ 128
The SET TRANSACTION Command: .................................................... 129
SQL Wildcard Operators ...................................................... 130
Syntax: ................................................................................................... 130
Example: ................................................................................................ 131
SQL Date Functions ............................................................. 132
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days) ............... 134
ADDTIME(expr1,expr2) .......................................................................... 135
CONVERT_TZ(dt,from_tz,to_tz) ............................................................ 135
CURDATE() ........................................................................................... 135
CURRENT_DATE and CURRENT_DATE() ........................................... 136
CURTIME() ............................................................................................ 136
CURRENT_TIME and CURRENT_TIME() ............................................. 136
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() .................... 136
DATE(expr) ............................................................................................ 136
DATEDIFF(expr1,expr2) ........................................................................ 136
DATE_ADD(date,INTERVAL expr unit),................................................. 137
DATE_SUB(date,INTERVAL expr unit) .................................................. 137
DATE_FORMAT(date,format) ................................................................ 138
DATE_SUB(date,INTERVAL expr unit) .................................................. 140
DAY(date) .............................................................................................. 140
DAYNAME(date) .................................................................................... 140
DAYOFMONTH(date) ............................................................................ 140
DAYOFWEEK(date) ............................................................................... 140

TUTORIALS POINT
Simply Easy Learning

Common questions

Powered by AI

Data types in SQL determine the kind of data that can be stored in a table column, impacting both storage requirements and data processing efficiency. Exact numeric data types store fixed-precision numbers, approximate types handle floating-point numbers, and character types manage text. Choosing the correct data type optimizes storage and improves data retrieval and manipulation performance, affecting database design and function execution .

FULL JOIN is preferable when you need results that include all records from both involved tables, combining matches and including all unmatched rows from both tables. This is useful when analyzing datasets where complete information from both sides is required, even if there are no direct matches in the joining condition, ensuring no data is omitted .

The GROUP BY clause in SQL is significant for aggregating rows that have the same values in specified columns into summary rows, such as totals or averages using aggregation functions like COUNT, SUM, AVG, and MAX. It groups the result-set by one or more columns and performs a specified aggregation on each group, making it essential for generating statistical reports from data .

TRANSACTION control commands ensure data consistency by managing operations as a unit of work. COMMIT saves all changes made during the transaction in the database permanently, while ROLLBACK undoes all commands within a transaction in case of errors, restoring the database to its previous stable state, which is crucial for maintaining integrity and consistency .

Indexes in SQL databases improve query performance by allowing faster data retrievals through sorting and efficient access paths. However, they add overhead for data manipulation operations such as INSERT, UPDATE, and DELETE, which may degrade performance due to index maintenance. Therefore, indexes should be used judiciously, especially in write-heavy transactions .

The SQL WHERE clause functions as a filter by specifying conditions that rows must meet to be included in the query result-set, allowing users to extract only relevant data entries. For example, "SELECT * FROM Employees WHERE Salary > 50000" retrieves only those records where the employee's salary is greater than 50,000 .

PRIMARY KEY constraints ensure each record in a table is unique and not null, serving as a unique identifier for table rows, which aids in data indexing and retrieval efficiency. UNIQUE constraints similarly ensure all values in a column are distinct, thereby maintaining data integrity by preventing duplication of data entries .

SQL commands are divided into several categories based on their functionality: DDL (Data Definition Language) is used for defining database structures like tables (e.g., CREATE, ALTER, DROP). DML (Data Manipulation Language) manages data within the database (e.g., SELECT, INSERT, UPDATE, DELETE). DCL (Data Control Language) involves permissions and access control (e.g., GRANT and REVOKE). DQL (Data Query Language) focuses on querying the database to retrieve data (primarily the SELECT statement).

The SQL ALIAS feature assigns a temporary name to a table or a column, enhancing query readability and clarity by simplifying complex queries or renaming returned columns for better understanding. This is particularly beneficial when the query involves complex expressions, derived columns, or large joins, aiding both readers and developers .

The HAVING clause complements the GROUP BY clause by allowing you to specify conditions on aggregate functions that cannot be used with the WHERE clause. It filters results after the aggregation process, while the WHERE clause filters rows before aggregation. This distinction enables complex queries that refine grouped data .

You might also like