Basic SQL Commands

1
SQL

• Structured Query Language

Mr. Bimal Kumar Ray Dept of IST Ravenshaw
University
2
An Introduction to SQL

• Structured Query Language, which is a computer
  language for storing, manipulating and retrieving
  data in relational database.
• SQL is an ANSI (American National Standards
  Institute) standard but there are many different
  versions of the SQL language.
• SQL is used to communicate with database.
• SQL are used to perform tasks such as create,
  alter, drop, insert, update and delete etc.
• SQL can execute queries against a database.
• SQL can retrieve data from a database.
• SQL can create new tables, view, index, trigger
  and cursor etc in a database.

3
An Introduction to SQL

• When a user wants to get some information from a
  database file, he can issue a QUERY.
• A query is a user request to retrieve data or
  information with a certain condition.
• SQL is a query language that allows user to
  specify the conditions.
• The user specifies a certain condition.
• The program will go through all the records in
  the database file and select those records that
  satisfy (searching) the condition.
• The result of the query will then be stored in
  form of a table.

4
Overview of SQL

• Allows users to access data in relational
  database management systems.
• Allows users to describe the data.
• Allows users to define the data in database and
  manipulate that data.
• Allows users to create view, stored procedure,
  functions in a database
• Allows users to create and drop databases and
  tables.

5
Overview of SQL

• SQL is the standard (4GL) language used to
  manipulate and retrieve data from these
  relational databases.
• SQL enables a programmer or database
  administrator to do the following
• Modify a database's structure
• Query a database for information
• Update the information of a database
• Add user permissions on databases or tables
• Change system security settings

6
Overview of SQL

• 1. Data Definition Language (DDL) is the part of
  SQL that allows a database user to create and
  restructure database object.
• CREATE Creates a database, new table, a view of
  a table, index, cursor and trigger or other
  object in database.
• ALTER Modifies an existing database object,
  such as a table,view and index etc.
• DROP Deletes an entire table, a view of a table
  or other object in the database.
• RENAME Used to renaming table, view etc
• TRUNCATE Used to delete record permanente of a
  table.

7
Overview of SQL

• 2. Data Manipulation Language (DML) is the part
  of SQL used to manipulate data within objects of
  a relational database.
• INSERT used to insert a record on the table.
• UPDATE modification of records in the table.
• DELETE used to remove records in the table.
• 3. Data Query Language (DQL) A query language is
  a language in which a user requests information
  from a table.
• SELECT used to retrieve certain records from one
  or more table.

8
Overview of SQL

• 4. Data Control Language (DCL) SQL allow you to
• control access to data within the database. DCL
  normally
• used to create objects related to user access and
  also
• control the distribution of privileges amoung
  users.
• GRANT is used to grant both system-level and
  object-level privileges to an exting database
  user accounts.
• REVOKE To removes privileges that have been
  granted to database users.
• 5. Transaction Control Language (TCL) commands
  are used to manage transactions in database.These
  are used to manage the changes made by
  DML Statement.
• COMMIT Used to permanently save any database
  transactions.
• ROLLBACK Used restores the database to last
  commited state.
• SAVEPOINT Used to temporarily save a
  transaction.

9
Popular SQL Implementations

• Structured Query Language is a special-purpose
• Programming language designed for managing data
  held
• in a relational database management system
  (RDBMS),
• or for stream processing in a relational data
  stream
• management system (RDSMS).
• implementations are SQL, SQL Server, Oracle,
• SQL Plus, PL/SQL, MySQL, SQLite and PostgreSQL.
• According to DB-Engines, the most popular systems
  are Oracle, MySQL, Microsoft SQL Server,
  PostgreSQL and IBM DB2

10
Open Database Connectivity (ODBC)

• ODBC is a functional library designed to provide
  a common
• Application Programming Interface (API)
  to under database systems.
• ODBC driver uses the Standard SQLServer
  components for communicating from a client
  application to the database server.
• ODBC driver manager is a very thin layer that
  manages the
• communications between the application
  and any ODBC drivers
• with which the application works.
• ODBC driver is a single DLL that responds to all
  calls the application makes to the ODBC API.
• The driver communicates with the server through
  the SQL Server
• Net-Libraries using the SQL Server
  application-level protocol called Tabular Data
  Stream (TDS).

11

• Data Definition Language (DDL) DDL is the part
  of SQL that allows a database user to create and
  restructure database object.
• The storage structure and access methods used by
  the database system are specified by a set of
  defitions in a special type of DDL called a data
  storage and defition language.
• How To Create Database
• Creates a new database and the files used to
  store the database, or attaches a database from
  the files of a previously created database.
•  Syntax CREATE DATABASE ltdatabase namegt
• Example CREATE DATABASE baba

12

• SP_HELPDB
• Reports information about a specified database or
  all databases.
• sp_helpdb reports on all databases in
  master.dbo.sysdatabases.
• Syntax sp_helpdb ltdatabase namegt
• Example SP_HELPDB baba
• How To Renaming Database
• SP_RENAMEDB Changes the name of a database.
• Syntax sp_renamedb 'old_name' , 'new_name'
• This example changes the name of the baba
  database to babaswami.
• Example sp_renamedb baba', babaswami'
  
• Example EXEC sp_renamedb babaswami',
  IST'

13

• How To Open Database
• USE STATEMENT The SQL USE statement is used to
  select any existing database in SQL schema. then
  before starting your operation, you would need to
  select a database where all the operations would
  be performed.
• Syntax USE ltDatabaseNamegt
• Example USE IST
• How To Dropping Database
• Removes one or more user databases from an
  instance of SQL Database server.
• For used in Drop statement for remove a existing
  database.
• Syntax DROP DATABASE lt DATABASENAMEgt
• Example DROP DATABASE IST

14

• DATA TYPES IN SQL
• Data is collection of information strored in a
  database as
• one of several different data types.
• Data types are used to provide rules for data for
  particular
• columns.
• A data type deals with the way values are stored
  in a column as far as the length allocated for a
  column and values such as alphanumeric,
  numeric,date and time data are allowed.
• Char(n) Fixed length character string, with
  user-specified length n.
• Varchar(n) Variable length character strings,
  with user-specified maximum
  length n.
• Int Integer (a finite subset of the integers
  that is machine-dependent).
• Real/double/decimal Floating point and
  double-precision floating point numbers, with
  machine-dependent precision.
• Float(n) Floating point number, with
  user-specified precision of at least n digits.
• Date Dates, containing a (4 digit) year, month
  and date(yy-mm-dd)
• Time Time of day, in hours, minutes and
  seconds.

15

• Table in SQL
• A table is a database object used to store data.
• The data in a table is organized in rows and
  columns.
• Each row in a table represents a unique record
  and each column
• represents an attribute.
• The column names within a table must be unique,
  but the same
• column name can be used in different tables
  within a database.
• Naming Rules for Table and Columns
• Table names and column names must begin with a
  letter and can be 1-30 characters long.
• Names must contain only characters (A-Z, a-z,0-9
  and underscore)
• Names must not duplicate the of another object.
• Name must not be an SQL keywords and data type.

16

• How to Create a Table in SQL
• Creating a basic table involves naming the table
  and defining its
• columns and each column's data type.
• The SQL CREATE TABLE statement is used to create
  a new table.
• Syntax CREATE TABLE lttablenamegt
  (Fieldname1 datatype (size),Fieldname2
  datatype (size), Fieldname3 datatype
  (size))
• CREATE TABLE is the keyword telling the database
  system what you want to do. In this case, you
  want to create a new table.
• Then in brackets comes the list defining each
  column in the table and what sort of data type it
  is.
• Example Create table student ( stroll varchar
  (10) primary key, sname char (20) not null,
  sdept text, sage int, sfees float )

17

• SP_HELP
• This command is used to describe all information
  for database objects.
• Syntax Sp_Help lttable namegt
• Example for Sp_Help Student
• SP_COLUMNS
• To returns all the information details for
  columns on database tabels.
• Syntax Sp_columns ltTable Namegt Example
  Sp_columns student
• How to Modifying the Table
• A table can be modified though the use of the
  ALTER TABLE command after
• that tables creation. You can add columns, drop
  columns, change column
• definitions, add and drop constraints.
• To Adding another one or more Columns for
  Existing Table
• To add another column in a exists table alter
  statement is used.
• Syntax Alter Table lttable_namegt Add
  ltcolumn_name data type(size)gt
• Example Alter Table student Add sphone int

18

• Modifying Existing Columns
• To modifying existing columns of a table.
• The length of a column can be increased to the
  maximun length of the given data type.
• The length of a column can be decreased only if
  the largest values for that column in the table
• is less than or equal to the new length of the
  column.
• The number of digits for a number datatype can
  always be increased.
• Syntax Alter Table ltTable_namegt Alter Column
• lt oldfieldname
  datatype(size)gt
• Example Alter Table student alter column sphone
  varchar(11)

19

• To Renaming a Columns
• To changes the column of field names for the
  database object.
• Syntax
• Sp_rename ltTablename.oldfield_namegt, ltnew
  field_namegt, column
• ExampleSp_rename student.st_roll, ROLLNO,
  column
•  
• To Renaming a Table
• To changes the TABLE names for the database
  object.
• Syntax Sp_rename ltold table namegt, ltnew table
  namegt
• Example Sp_rename student, ist_student
• To Dropping a Column
• The SQL DROP COLUMN statement is used to remove a
  table definition and columns permission
  specifications for that table.
• Syntax Alter Table ltTable_namegt Drop Column lt
  column namegt
• Example Alter Table student Drop Column
  sphone
•  

20

• Truncating a Table
• it is used to remove all records from a table
  and to release the storgage
• space used by that table. You can not rollback
  records removal.
• Syntax Truncate Table lttable_namegt
• Example Truncate Table student
•  
• Dropping a Table
• Dropping a table is actually one of the easiest
  things to do. When the
• RESTRICT option is used and the table is
  referenced by a view or
• constraint, the drop statement returns an error.
  When the CASCADE
• option is used, the drop successds and
  referencing views and
• constrainsts are dropped.
• Syntax Drop Table lttable_namegt RESTRICT or
  CASCADE
• Example Drop Table student
•  

21

• Primary Key
• Primary key is the method, which uniquely
  identifies each row in a table.
• It is a column or group column that enforce
  entity integrity by ensuring that each instance
  of the entity is unique.
• Every table must have a primary key.
• Only one primary key can exist per table.
• The primary key shouldnt allow any change or
  duplicate.
• The column in a primary key constraint cannot be
  null.

22

• Foreign Key
• A foreign key helps in maintaining referential
  integrity among specified table with the primary
  key.
• A foreign key of one table has to be a primary
  key of some other table.
• A primary key of same table or another table can
  be declared as foreign key.
• All the values listed in a foreign key column are
  tested in the corresponding primary key column.
• Ensure referential integreity by ensuring the
  value on the foreign key column is a valid
  primary key value.
• It maintains the link between two tables or more.

23

• What is Null Value?
• A NULL value in a table is a value in a field
  that appears to be blank, which means a field
  with a NULL value is a field with no value.
• Columns of any data type can contain null values,
  unless the column was defined as NOT NULL or as
  primary key when the column was created.
• A field with a NULL value is one that has been
  left blank during record creation.
• A null is not the same as zero or a blank space.
• A null is value that is unavilable, unassigned,
  unknown or inapplicable.

24

• How to Insert Data Values in to the Tables
• After creating table the values are to be
  inserted in to the table.
• The SQL INSERT INTO Statement is used to add new
  rows of
• data to a table in the database.There are two
  basic syntaxes of
• INSERT INTO statement as follows
• Syntax INSERT INTO TABLE_NAME (column1,
• column2,columnN) VALUES (value1,
  value2,valueN)
• insert into table_name values (value1,value2,value
  3,...valueN)
• Use the INSERT command to insert a new row of
  data into an
• existing table. In a table maximum 1024 column 7
  8092 bytes is
• allowed per row.
• Example INSERT INTO student VALUES
  (14dis001 ,Aani,kataka,20, 5000)

25

• UPDATING DATA FROM TABLES
• Use the UPDATE command to update one or more
  columns in an existing row of data in a table.
• The update statement is used to modify one or
  more rows of data in a single table.
• The table owner has the rights to update the rows
  can transfer this right to another user.
• For different values where clause according to
  our need.
• For fixing a constant value no where clause is
  used since it sets a default value to the
  specific column.
• A table can also be updated with an arithmetic
  expression.
• If the updated values violate the data type, on
  that column, the update fails the changes are
  rolled back.

26

• UPDATING DATA FROM TABLES
• If you are updating a single row and you know the
  value of the primary key for the row you wish to
  update, specify the value using the optional
  WHERE clause to improve the performance of the
  update command.
• If there is more than one primary key column, the
  values must be specified in order, separated by
  commas and in parentheses. Columns that have
  string values must be enclosed in single quotes.
• SYNTAX UPDATE lttable_namegt SET column_name
  
• expression,... WHERE col-name condition
  

27
Thank You