A Practical
File on
STRUCTURED
QUERY LANGUAGE
Submitted to: Submitted by:
Ms. Jyoti Gaba Jyoti Chauhan
Ms. Priyanka Grover B.com final yr.
(Assistant prof. in Computer App.) Roll no. 4570
Acknowledgement
As a student from B.com 3rd yr. I did the project file,
as a part of studies during session 2016-17.
I own a deep sense of gratitude towards the principle
who enthusiasm and inspire to do everything.
I also thankful to my teachers Ms. Jyoti Gaba &
Ms. Priyanka Grover for their guidance,
supervision and advice. Finally, I thank to my
parents for their supporting me.
Jyoti Chauhan
B.com 3rd yr.
INDEX
S.no. PARTICULARS SIGNATURE REMARKS
1 Introduction
2 Advantages & Disadvantages
3 Uses & Applications
4 Create Table
5 Insert
6 Select
7 Order By
8 Alter Table
9 Update
10 Modify Column
11 Drop Table
12 Where Condition
13 Mathematical Functions
14 Count
15 Select Distinct
16 Max. & Min.
17 Describe Table
18 Delete Table
19 Renaming File
20 Constraints (not null,primary key,unique etc)
INTRODUCTION TO SQL
SQL Structured Query Language is a special-purpose domain-
specific language used in programming and designed for managing
data held in a relational database management system (RDBMS), or
for stream processing in a relational data stream management
system (RDSMS).
Originally based upon relational algebra and tuple relational calculus,
SQL consists of a data definition language, data manipulation
language, and data control language. The scope of SQL includes data
insert, query, update and delete, schema creation and modification,
and data access control. Although SQL is often described as, and to a
great extent is, a declarative language (4GL), it also
includes procedural elements.
The model was first introduced in a paper titled "A Relational Model
of Data for Large Shared Data Banks" written by E. F. Codd. The
language was at one point titled SEQUEL for Structured English
Query Language; you may occasionally still hear SQL pronounced
that way.
SQL-related products have been in commercial use since 1979, when
they were introduced by a forerunner to Oracle. Does Oracle control
SQL? No. IBM soon followed suit. Today SQL is used in many open
source and commercial products. Often these include SQL in the
name. Postgre SQL is among the best known.
SQL is used in health care (cancer registries) business (inventories,
trends analysis), and education. It even has applications in the
defense industry.
There are two standards committees "guarding" SQL: the
International Standards Organization and the American National
Standards Institute. Still, SQL is not all the same. In order to access
and use the data, you need a database program or server. The major
vendors have their own servers for database applications. Oracle
Database applications use the Oracle Database SQL. IBM’s server is
DB2. There’s also the Microsoft SQL Server. Then there’s MySQL ,
an open source database program that is sometimes thought of as a
light version of Microsoft SQL – a place to get started, but one your
business may ultimately outgrow.
While multiple database programs use the SQL standard, they may
eliminate portions or add to it. PL/SQL, for example, is oracle’s
extension. Alterations make for some compatibility issues – this is an
area of continued focus.
(SQL is a standardized query language for requesting information
from a database. The original version called SEQUEL (structured
English query language) was designed by an IBM research center in
1974 and 1975.SQL was first introduced as a commercial database
system in 1979 by Oracle Corporation.)
Advantages of SQL:
• High Speed: SQL Queries can be used to retrieve large
amounts of records from a database quickly and efficiently.
• Well Defined Standards Exist: SQL databases use long-
established standard, which is being adopted by ANSI & ISO.
Non-SQL databases do not adhere to any clear standard.
• No Coding Required: Using standard SQL it is easier to
manage database systems without having to write substantial
amount of code.
• Emergence of ORDBMS: Previously SQL databases were
synonymous with relational database. With the emergence of
Object Oriented DBMS, object storage capabilities are
extended to relational databases.
Disadvantages of SQL:
• Difficulty in Interfacing: Interfacing an SQL database is
more complex than adding a few lines of code.
• More Features Implemented in Proprietary way: Although
SQL databases conform to ANSI & ISO standards, some
databases go for proprietary extensions to standard SQL to
ensure vendor lock-in.
• SQL is a database language, used to update databases, execute
queries, and manage permissions. It was designed for relational
database management systems. It’s generally considered a
declarative language. It's been around in some form since the 70s
and is just about as ubiquitous as data management itself.
Uses of SQL:
• SQL is used in health care (cancer registries) business
(inventories, trends analysis), and education. It even has
applications in the defense industry.
• Who works with SQL? Database developers and administrators
and business analysts are among the better known users. But
knowing at least a little SQL can be an asset for people in a lot of
different roles, from the web developer to the PhD level scientist.
The most basic task is the query -- you specify "from" to tell the
program what table(s) to retrieve data from and give additional
clauses like "where" and "having" to narrow down what you
want. A more advanced user will design and modify databases.
The characteristics of SQL statements pertaining to the various places
where they are used.
• Actions allowed on SQL statements shows whether an SQL
statement can be executed, prepared interactively or dynamically,
and whether the statement is processed by the requester, the server
or the precompiler.
• SQL statement data access classification for routines shows the
level of SQL data access that must be specified to use the SQL
statement in a routine.
• Considerations for using distributed relational database provides
information about the use of SQL statements when the application
server is not the same as the application requester.
CREATE TABLE
CREATE TABLE table_name (column_1 datatype, column_2
datatype, column_3 datatype);
CREATE TABLE creates a new table in the database. It allows you to
specify the name of the table and the name of each column in the
table.
INSERT
'value INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, _2', value_3);
INSERT statements are used to add a new row to a table.
SELECT
SELECT column_name FROM table_name;
SELECT statements are used to fetch data from a database. Every
query will begin with SELECT.
When data from all rows and columns from the table are to be viewed
the syntax of the SELECT statement will be:
SELECT*from tablename;
ORDER BY
SELECT column_name
FROM table_name
ORDER BY column_name ASC|DESC;
ORDER BY is a clause that indicates you want to sort the result set
by a particular column either alphabetically or numerically.
ALTER TABLE
ALTER TABLE table_name ADD column datatype;
ALTER TABLE lets you add columns to a table in a database.
UPDATE
UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;
UPDATE statements allow you to edit rows in a table.
MODIFY COLUMN:
Sometimes we need to change the data type of a column. To do this,
we use the ALTER TABLE Modify Column command. For Oracle
and MySQL, the SQL syntax for ALTER TABLE Modify Columns,
ALTER TABLE "table_name"
MODIFY "column_name" "New Data Type";
For SQL Server, the syntax is,
ALTER TABLE "table_name"
ALTER COLUMN "column_name" "New Data Type";
DROP TABLE:
The SQL DROP TABLE statement is used to remove a table
definition and all data, indexes, triggers, constraints, and permission
specifications for that table.
Basic syntax of DROP TABLE statement is as follows:
DROP TABLE table name;
WHERE
SELECT column_name(s)
FROM table_name
WHERE column_name operator value;
WHERE is a clause that indicates you want to filter the result set to
include only rows where the following condition is true.
MATHEMATICAL FUNCTIONS:
The modulo operator determines the integer remainder of the division.
This operator is not ANSI SQL supported; however, most databases
support it. The following are some more useful mathematical
functions to be aware of since you might need them. These functions
are not standard in the ANSI SQL-92 specs; therefore they may or
may not be available on the specific RDBMS that you are using.
ABS(x) returns the absolute value of x
returns the sign of input x as -1, 0, or 1 (negative,
SIGN(x)
zero, or positive respectively)
modulo - returns the integer remainder of x
MOD(x,y)
divided by y (same as x%y)
returns the largest integer value that is less than or
FLOOR(x)
equal to x
CEILING(x) or returns the smallest integer value that is greater
CEIL(x) than or equal to x
POWER(x,y) returns the value of x raised to the power of y
returns the value of x rounded to the nearest
ROUND(x)
whole integer
returns the value of x rounded to the number of
ROUND(x,d)
decimal places specified by the value d
SQRT(x) returns the square-root value of x
COUNT
SELECT COUNT(column_name)
FROM table_name;
COUNT() is a function that takes the name of a column as an
argument and counts the number of rows where the column is
not NULL.
SELECT DISTINCT
SELECT DISTINCT column_name FROM table_name;
SELECT DISTINCT specifies that the statement is going to be a
query that returns unique values in the specified column(s)
MAX
SELECT MAX(column_name)
FROM table_name;
MAX() is a function that takes the name of a column as an argument
and returns the largest value in that column.
MIN
SELECT MIN(column_name)
FROM table_name;
MIN() is a function that takes the name of a column as an argument
and returns the smallest value in that column.
DESCRIBE TABLE:
Describe command display the column names, the data types and the
special attributes connected to the table. Syntax is:
DESCRIBE tablename;
DELETE TABLE:
The verb DELET in SQL is used to remove rows from table. Syntax
is:
DELETE FROM tablename;
RENAMING TABLE:
To rename a table, the syntax is:
RENAME oldtablename TO newtablename;
DEFAULT, PRIMARY KEY, UNIQUE, NOT NULL
CONSTRAINTS
