[PDF] [PDF] SQL i - Tutorialspoint

[PDF] Chapter 8 Advanced SQL

In this chapter, the student will learn: ▫ How to use the advanced SQL JOIN operator syntax ▫ About the different types of subqueries and correlated queries

[PDF] Advanced SQL Database Programmers Handbook

31 déc 2020 · This book dives deep into the internals of Oracle programming problems and presents challenging and innovative solutions to complex data 

[PDF] SQL Tutorial

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

[PDF] SQL i - Tutorialspoint

advanced concepts related to SQL languages This tutorial will give you enough understanding on the various components of SQL along with suitable examples

[PDF] SQL & Advanced SQL - CERN Indico

5 mai 2012 · Hierarchical QUERIES What is the hierarchy of management in my enterprise? ADVANCED SQL QUERIES Oracle Tutorials 5th of May 2012 

[PDF] Advanced SQL and Functions - Joe Conway

17 sept 2014 · http://www postgresql org/docs/9 4/interactive/sql-select html Queries Syntax Overview- VALUES, TABLE Last, but not least, the most 

[PDF] Advanced SQL

4 5 Writing queries in SQL is typically much easier than coding the same queries in a general-purpose programming language However not all kinds of queries 

[PDF] Advanced SQL - Subqueries and Complex Joins

The URISA Proceedings database - more practice with increasingly complicated SQL queries • Advanced Queries: o Sub-queries: one way to nest or a cascade 

[PDF] Teach Yourself SQL in 21 Days, Second Edition - M-Auwal Gene IIIs

We begin with the simplest of SQL statements and advance to the topics of transaction management and stored procedure programming The Oracle RDBMS is 

[PDF] sql cheat sheet for interview

[PDF] sql cheat sheet github

[PDF] sql cheat sheet reddit

[PDF] sql date cheat sheet

[PDF] sql dimensional modeling

[PDF] sql exercises

[PDF] sql file naming conventions

[PDF] sql functions pdf

[PDF] sql interview questions for experienced professionals

[PDF] sql interview questions for testers

[PDF] sql interview questions pdf for freshers

[PDF] sql interview questions pdf tutorialspoint

[PDF] sql naming conventions github

[PDF] sql projects for practice

[PDF] sql queries examples pdf free download

SQL i SQL i SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. This tutorial will give you a quick start to SQL. It covers most of the topics required for a basic understanding of SQL and to get a feel of how it works. This tutorial is prepared for beginners to help them understand the basic as well as the advanced concepts related to SQL languages. This tutorial will give you enough understanding on the various components of SQL along with suitable examples. Before you start practicing with various types of examples given in this tutorial, I am assuming that you are already aware about what a database is, especially the RDBMS and what is a computer programming language. If you are willing to compile and execute SQL programs with Oracle 11g RDBMS but you dedicated server giving you real programming experience. It is free and is available online for everyone.

Copyright 2018 by Tutorials Point (I) Pvt. Ltd.

All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at contact@tutorialspoint.com SQL ii

About the Tutorial ............................................................................................................................................ i

Audience ........................................................................................................................................................... i

Prerequisites ..................................................................................................................................................... i

Compile/Execute SQL Programs ....................................................................................................................... i

Copyright & Disclaimer ..................................................................................................................................... i

Table of Contents ............................................................................................................................................ ii

1. SYL ൞ Oǀerǀiew ......................................................................................................................................... 1

What is SQL? .................................................................................................................................................... 1

SQL Process ..................................................................................................................................................... 2

SQL Commands ................................................................................................................................................ 3

2. SYL ൞ RDBMS Concepts ............................................................................................................................. 5

What is RDBMS? .............................................................................................................................................. 5

SQL Constraints ............................................................................................................................................... 6

Data Integrity ................................................................................................................................................... 7

Database Normalization .................................................................................................................................. 7

Database - First Normal Form (1NF) ............................................................................................................... 8

Database - Second Normal Form (2NF) ........................................................................................................ 10

Database - Third Normal Form (3NF) ............................................................................................................ 11

3. SYL ൞ RDBMS Databases ......................................................................................................................... 14

MySQL ........................................................................................................................................................... 14

MS SQL Server ............................................................................................................................................... 15

ORACLE .......................................................................................................................................................... 16

MS ACCESS ..................................................................................................................................................... 17

4. SQL - Syntax ........................................................................................................................................... 19

Various Syntax in SQL .................................................................................................................................... 19

5. SYL ൞ Data Types .................................................................................................................................... 24

6. SQL - Operators ...................................................................................................................................... 28

What is an Operator in SQL? ......................................................................................................................... 28

SQL Arithmetic Operators ............................................................................................................................. 28

Arithmetic Operators - Examples ................................................................................................................. 29

SQL Comparison Operators ........................................................................................................................... 30

Comparison Operators - Examples ............................................................................................................... 31

SQL Logical Operators ................................................................................................................................... 34

Logical Operators - Examples........................................................................................................................ 35

7. SQL - Expressions ................................................................................................................................... 41

Boolean Expressions ...................................................................................................................................... 41

Numeric Expressions ..................................................................................................................................... 42

Date Expressions ........................................................................................................................................... 43

8. SQL - CREATE Database .......................................................................................................................... 45

9. SYL ൞ DROP or DELETE Database ............................................................................................................. 46

10. SYL ൞ SELECT Database, USE Statement .................................................................................................. 47

SQL iii

11. SYL ൞ CREATE Table ................................................................................................................................ 48

SQL - Creating a Table from an Existing Table ............................................................................................... 49

12. SYL ൞ DROP or DELETE Table ................................................................................................................... 51

13. SYL ൞ INSERT Yuery ................................................................................................................................ 53

14. SYL ൞ SELECT Yuery ................................................................................................................................ 56

15. SYL ൞ WHERE Clause ............................................................................................................................... 59

16. SYL ൞ AND Θ OR Conjunctiǀe Operators ................................................................................................. 62

The AND Operator ......................................................................................................................................... 62

The OR Operator ........................................................................................................................................... 63

17. SYL ൞ UPDATE Yuery .............................................................................................................................. 66

18. SYL ൞ DELETE Yuery ................................................................................................................................ 69

19. SYL ൞ LIKE Clause .................................................................................................................................... 72

20. SYL ൞ TOP, LIMIT or ROWNUM Clause .................................................................................................... 76

21. SYL ൞ ORDER BY Clause ........................................................................................................................... 79

22. SYL ൞ Group By ....................................................................................................................................... 82

23. SQL ൞ Distinct Keyword ........................................................................................................................... 86

24. SYL ൞ SORTING Results ........................................................................................................................... 89

25. SYL ൞ Constraints .................................................................................................................................... 92

SQL - NOT NULL Constraint ........................................................................................................................... 92

SQL - DEFAULT Constraint ............................................................................................................................. 93

SQL - UNIQUE Constraint ............................................................................................................................... 94

SYL ൞ Primary Key.......................................................................................................................................... 95

SYL ൞ Foreign Key .......................................................................................................................................... 96

SYL ൞ CHECK Constraint ................................................................................................................................ 98

SQL ൞ INDEy Constraint ................................................................................................................................. 99

Dropping Constraints ................................................................................................................................... 101

Integrity Constraints .................................................................................................................................... 101

26. SYL ൞ Using Joins .................................................................................................................................. 102

SQL - INNER JOIN ......................................................................................................................................... 103

SYL ൞ LEFT JOIN ........................................................................................................................................... 105

SQL - RIGHT JOIN ......................................................................................................................................... 107

SYL ൞ FULL JOIN ........................................................................................................................................... 109

SYL ൞ SELF JOIN ........................................................................................................................................... 111

SYL ൞ CARTESIAN or CROSS JOIN ................................................................................................................. 113

27. SYL ൞ UNIONS CLAUSE .......................................................................................................................... 116

The UNION ALL Clause ................................................................................................................................. 119

SYL ൞ INTERSECT Clause .............................................................................................................................. 121

SYL ൞ EyCEPT Clause ................................................................................................................................... 123

SQL iv

28. SYL ൞ NULL Values ................................................................................................................................ 127

29. SYL ൞ Alias Syntadž ................................................................................................................................. 130

30. SQL - Indexes ........................................................................................................................................ 134

The CREATE INDEX Command ..................................................................................................................... 134

The DROP INDEX Command ........................................................................................................................ 135

SQL - INDEX Constraint ................................................................................................................................ 135

31. SYL ൞ ALTER TABLE Command .............................................................................................................. 138

32. SQL - TRUNCATE TABLE Command ........................................................................................................ 142

33. SYL ൞ Using Views ................................................................................................................................. 143

Creating Views ............................................................................................................................................. 143

The WITH CHECK OPTION ............................................................................................................................ 144

34. SYL ൞ Haǀing Clause .............................................................................................................................. 148

35. SQL - Transactions ................................................................................................................................ 151

Properties of Transactions ........................................................................................................................... 151

Transactional Control Commands ............................................................................................................... 151

36. SYL ൞ Wildcard Operators ..................................................................................................................... 158

37. SYL ൞ Date Functions ............................................................................................................................ 162

38. SYL ൞ Temporary Tables ........................................................................................................................ 192

What are Temporary Tables? ...................................................................................................................... 192

Dropping Temporary Tables ........................................................................................................................ 193

39. SQL - Clone Tables ................................................................................................................................ 194

40. SQL - Sub Queries ................................................................................................................................. 197

Subqueries with the SELECT Statement ...................................................................................................... 197

Subqueries with the INSERT Statement ...................................................................................................... 198

Subqueries with the UPDATE Statement ..................................................................................................... 199

Subqueries with the DELETE Statement ...................................................................................................... 200

41. SQL - Using Sequences.......................................................................................................................... 202

Using AUTO_INCREMENT column ............................................................................................................... 202

Obtain AUTO_INCREMENT Values .............................................................................................................. 203

Renumbering an Existing Sequence ............................................................................................................ 203

Starting a Sequence at a Particular Value ................................................................................................... 204

42. SQL - Handling Duplicates .................................................................................................................... 206

43. SQL - Injection ...................................................................................................................................... 209

Preventing SQL Injection ............................................................................................................................. 210

SQL 1 SQL is a language to operate databases; it includes database creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of the SQL language. SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

Also, they are using different dialects, such as:

MS SQL Server using T-SQL,

Oracle using PL/SQL,

MS Access version of SQL is called JET SQL (native format) etc.

Why SQL?

SQL is widely popular because it offers the following advantages: Allows users to access data in the relational database management systems.

Allows users to describe the data.

Allows users to define the data in a database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views.

A Brief History of SQL

1970 ± Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational

databases. He described a relational model for databases.

1974 ± Structured Query Language appeared.

1978 ± IBM worked to develop Codd's ideas and released a product named

System/R.

1. SQL ൞ Overview

SQL 2

1986 ± IBM developed the first prototype of relational database and standardized

by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle. When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. There are various components included in this process.

These components are ±

Query Dispatcher

Optimization Engines

Classic Query Engine

SQL Query Engine, etc.

A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical files. Following is a simple diagram showing the SQL Architecture: SQL 3 The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature:

DDL - Data Definition Language

Command Description

CREATE Creates a new table, a view of a table, or other object in thequotesdbs_dbs20.pdfusesText_26