SQL Introduction
What is SQL?
- SQL is a widely-used programming language that allows you to define and query databases. Whether you’re a marketing analyst, a journalist, or a researcher mapping neurons in the brain of a fruit fly, you’ll benefit from using SQL to manage database objects as well as create, modify, explore, and summarize data.
- SAS defines Structured Query Language (SQL) as “a standardized, widely used language that retrieves data from and updates data in tables and the views that are based on those tables.
- it has been implemented by many vendors, and is especially widespread in the relational database management system (RDBMS) world.
- SQL (Structured Query Language) is used to perform operations on the records stored in the database, such as updating records, inserting records, deleting records, creating and modifying database tables, views,etc.
- SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell.
- The SQL standard is maintained by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
- There are three categories of SQL syntax term: identifiers, literals, and keywords.
Why Use SQL?
- SQL database system allows you to work with terabytes of data, multiple related tables, and thousands of columns.
- It gives you improved programmatic control over the structure of your data, leading to efficiency, speed, and—most important—accuracy.
- SQL is also an excellent adjunct to programming languages used in the data sciences, such as R and Python.
- SQL often serves as an easy-to-understand introduction into concepts related to data structures and programming logic.
- you’ll find that databases provide the backend power for many common web frameworks, interactive maps, and content management systems.
RDBMS
- RDBMS-Stands for Relational Database Management System.
- RDBMSes store data in the form of tables, with most commercial relational database management systems using Structured Query language (SQL) to access the database.
- An RDBMS includes functions that maintain the security, accuracy, integrity and consistency of the data.
- Some examples of specific systems that use RDBMS include IBM, Oracle, MySQL, Microsoft SQLServer and PostgreSQL
Other advantages of the RDBMS include:
- Flexibility — updating data is more efficient since the changes only need to be made in one place.
- Maintenance — database administrators can easily maintain, control and update data in the database. Backups also become easier since automation tools included in the RDBMS automate these tasks.
- Data structure — the table format used in RDBMSes is easy to understand and provides an organized and structural manner through which entries are matched by firing queries.
SQL statements may be divided into the following categories
- Data Query Language (DQL) : Statements that query the database but do not alter any data or database objects. This category contains the SELECT statement.
- Data Manipulation Language (DML) : Statements that modify data stored in database objects, such as tables. This category contains the INSERT, UPDATE, and DELETE statements.
- Data Definition Language (DDL) : Statements that create and modify database objects. Whereas DML and DQL work with the data in the database objects, DDL works with the database objects themselves. This category includes the CREATE, ALTER and DROP statements.
- Data Control Language (DCL) : Statements that manage privileges that database users have regarding the database objects. This category includes the GRANT and REVOKE statements.
Tуреѕ оf Exесutiоn
уоu саn соmmuniсаtе dirесtlу frоm a frоnt еnd аррliсаtiоn, ѕuсh аѕ iSQL*Pluѕ in Orасlе оr Mаnаgеmеnt Studio in Miсrоѕоft SQL Server, tо thе dаtаbаѕе. (Thе frontend application and thе dаtаbаѕе саn bе on thе ѕаmе соmрutеr, but often аrе nоt.)
- Embеddеd SQL: SQL statements are еnсоdеd (еmbеddеd) directly in the hоѕt рrоgrаmming language. For еxаmрlе, you саn еmbеd SQL statements within C аррliсаtiоn соdе. Bеfоrе the code iѕ соmрilеd, a preprocessor analyzes thе SQL ѕtаtеmеntѕ аnd ѕрlitѕthеm out from the C code.
- Module Binding: Thiѕ mеthоd аllоwѕ уоu tо create blосkѕ оf SQL ѕtаtеmеntѕ (mоdulеѕ) thаt аrе separate from the hоѕt рrоgrаmming lаnguаgе.
- Cаll-lеvеl intеrfасе (CLI):A CLI аllоwѕ you to invoke SQL ѕtаtеmеntѕ through аn interface by раѕѕing SQL ѕtаtеmеntѕ аѕ argument vаluеѕ to ѕubrоutinеѕ. The statements аrе nоt рrесоmрilеd аѕ thеу are in embedded SQL and module binding. Inѕtеаd, thеу аrе еxесutеd dirесtlу bу the RDBMS.
SQL Intеgrаtiоn Toolkits
| Lаnguаgе | Tооlkit |
| Jаvа | JDBC (Jаvа Database Connectivity; JаvаSоft) |
| C++ | Rоguе Wаvе SourcePro DB (third party tооl to connect to Orасlе, SQL Sеrvеr, MуSQL, Infоrmix, DB2, Sybase, and PоѕtgrеSQL dаtаbаѕеѕ) |
| C/C++ | Prо*C (Orасlе), MySQL C API (open ѕоurсе), аnd DB2 |
| Cаll Level Interface (IBM) # | |
| C# | ADO.NET (Miсrоѕоft) |
| Pеrl | Pеrl DBI |
| Pуthоn | Python DB |
| Viѕuаl Basic | ADO.NET (Miсrоѕоft) |
Structured Query Language contains the following four components in its process:
- Query Dispatcher
- Optimization Engines
- Classic Query Engine
- SQL Query Engine
Using SQL in Your Web Site
- An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
- To use a server-side scripting language, like PHP or ASP
- To use SQL to get the data you want
- To use HTML / CSS to style the page
- SQL database is to use a web framework, such as Django for Python or Rails for Ruby.
- The most popular database tools in the world, including some of Oracle’s database systems, MySQL, PostgreSQL and Microsoft’s SQL Server.
SQL Data Type
Defining Databases
| Databse Name | Syntax |
| SQL CREATE Database | CREATE DATABASE database_name; |
| Rename MySQL database | RENAME DATABASE old_db_name TO new_db_name; |
| SQL SELECT Database | USE DATABASE database_name; |
| SQL DROP TABLE Statement | DROP TABLE table_name; |
| SQL ALTER TABLE Statement | LTER TABLE table_name ADD column_name datatype; |
| SQL Constraints | CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, …. ); |
| SQL NOT NULL Constraint | CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int ); |
| SQL UNIQUE Constraint | CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); |
| SQL PRIMARY KEY Constraint | CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); |
| SQL FOREIGN KEY Constraint | CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); |
| SQL CHECK Constraint | CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) ); |
| SQL DEFAULT Constraint | CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT ‘Sandnes’ ); |
| SQL CREATE INDEX Statement | CREATE INDEX index_name ON table_name (column1, column2, …); |
| SQL AUTO INCREMENT Field | CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) ); |
| SQL Working With Dates | SELECT * FROM Orders WHERE OrderDate=’2021-08-05′ |
| SQL CREATE VIEW Statement | CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition; |
| SQL Injection | SELECT * FROM Users WHERE UserId = 100 OR 12=12; |
Book Pdf Download