What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
What is Database Design?
Database Design is a collection of processes that facilitate the designing, evelopment,
implementation and maintenance of enterprise data management systems It helps produce
database systems
1. That meet the requirements of the users
2. Have high performance.
The main objectives of database designing are to produce logical and physical designs
models of the proposed database system.
The logical model concentrates on the data requirements and the data to be stored
independent of physical considerations. It does not concern itself with how the data will be
stored or where it will be stored physically.
The physical data design model involves translating the logical design of the database onto
physical media using hardware resources and software systems such as database management
systems (DBMS).
Why Database Design is Important ?
Database designing is crucial to high performance database system.
Apart from improving the performance, properly designed database are easy to maintain,
improve data consistency and are cost effective in terms of disk storage space.
Note , the genius of a database is in its design . Data operations using SQL is relatively
simple
The database development life cycle has
a number of stages that are followed
when developing database systems.
The steps in the development life
cycle do not necessary have to be
followed religiously in a sequential
manner.
On small database systems, the
database system development life
cycle is usually very simple and does not involve a lot of steps.
In order to fully appreciate the above diagram, let's look at the individual components listed
in each step.
Requirements analysis
Planning - This stages concerns with planning of entire Database Development Life
Cycle It takes into consideration the Information Systems strategy of the
organization.
System definition - This stage defines the scope and boundaries of the proposed
database system.
Database designing
Logical model - This stage is concerned with developing a database model based on
requirements. The entire design is on paper without any physical implementations or
specific DBMS considerations.
Physical model - This stage implements the logical model of the database taking into
account the DBMS and physical implementation factors.
Implementation
Data conversion and loading - this stage is concerned with importing and converting
data from the old system into the new database.
Testing - this stage is concerned with the identification of errors in the newly
implemented system .It checks the database against requirement specifications.
Two Types of Database Techniques
1. Normalization
2. ER Modeling
==============
SQL (Structured Query Language)
It is a standard language for storing, manipulating and retrieving data in databases.
SQL (Structured Query Language) is a standardized programming language used for
managing relational databases and performing various operations on the data in them.
Initially created in the 1970s, SQL is regularly used by database administrators, as well as by
developers writing data integration scripts and data analysts looking to set up and run
analytical queries.
The uses of SQL include modifying database table and index structures; adding,
updating and deleting rows of data; and retrieving subsets of information from within a
database for transaction processing and analytics applications. Queries and other SQL
operations take the form of commands written as statements -- commonly used SQL
statements include select, add, insert, update, delete, create, alter and truncate.
SQL commands and syntax
SQL commands are divided into several different types
data manipulation language (DML) and data definition language (DDL)
statements, transaction controls and security measures. The DML
vocabulary is used to retrieve and manipulate data, while DDL statements
are for defining and modifying database structures. The transaction
controls help manage transaction processing, ensuring that transactions are
either completed or rolled back if errors or problems occur. The security
statements are used to control database access as well as to create user roles
and permissions.
DML statements are SQL statements that manipulate data. DML stands for Data
Manipulation Language. The SQL statements that are in the DML class are INSERT,
UPDATE and DELETE. Some people also lump the SELECT statement in the DML
classification.
Data Definition Languages (DDL) are used to define the database structure. Any CREATE,
DROP and ALTER commands are examples of DDL SQL statements.
SQL commands can be divided into four subgroups, DDL, DML, DCL, and TCL.
DDL
A data definition language (DDL) is a computer language used to create and modify the
structure of database objects in a database. These database objects include views, schemas,
tables, indexes, etc.
This term is also known as data description language in some contexts, as it describes the
fields and records in a database table. Commonly used DDL in SQL querying are:
CREATE: This command builds a new table and has a predefined syntax. The
CREATE statement syntax is CREATE TABLE [table name] ([column definitions])
[table parameters]. CREATE TABLE Employee (Employee Id INTEGER PRIMARY
KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL).
ALTER: An alter command modifies an existing database table. This command can
add up additional column, drop existing columns and even change the data type of
columns involved in a database table. An alter command syntax is ALTER object type
object name parameters. ALTER TABLE Employee ADD DOB Date.
DROP: A drop command deletes a table, index or view. Drop statement syntax is
DROP object type object name. DROP TABLE Employee.
TRUNCATE - remove all records from a table, including all spaces allocated for the
records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML
DML resembles simple English language and enhances efficient user interaction with the
system. The functional capability of DML is organized in manipulation commands like
SELECT, UPDATE, INSERT INTO and DELETE FROM, as described below:
SELECT: This command is used to retrieve rows from a table. The syntax is SELECT
[column name(s)] from [table name] where [conditions]. SELECT is the most widely
used DML command in SQL.
UPDATE: This command modifies data of one or more records. An update command
syntax is UPDATE [table name] SET [column name = value] where [condition].
INSERT: This command adds one or more records to a database table. The insert
command syntax is INSERT INTO [table name] [column(s)] VALUES [value(s)].
DELETE: This command removes one or more records from a table according to
specified conditions. Delete command syntax is DELETE FROM [table name] where
[condition].
DCL
It is short name of Data Control Language which includes commands such as GRANT and
mostly concerned with rights, permissions and other controls of the database system.
GRANT - allow users access privileges to database
REVOKE - withdraw users access privileges given by using the GRANT command
TCL
It is short name of Transaction Control Language which deals with a transaction within a
database.
COMMIT - commits a Transaction
ROLLBACK - rollback a transaction in case of any error occurs
SAVEPOINT - to rollback the transaction making points within groups
SET TRANSACTION - specify characteristics of the transaction
Query
A query is a question, often expressed in a formal way. A databasequery can be either a
select query or an action query. A select query is a data retrieval query, while an action query
asks for additional operations on the data, such as insertion, updating or deletion.
Query language
Query languages are used to make queries in a database, and Microsoft Structured Query
Language (SQL) is the standard. Under the SQL query umbrella, there are several extensions
of the language, including MySQL, Oracle SQL and NuoDB. Query languages for other
types of databases, such as NoSQL databases and graph databases, include Cassandra Query
Language (CQL), Neo4j's Cypher, Data Mining Extensions (DMX) and XQuery.
