chapter 8 SQL.ppt

  quick introduction of SQL  create database and table  change table(new column, update column)  Insert query  select query  where clauses  update query  delete query  create an index  group by query 1 Chapter Outline

 What is SQL?  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. 2 Quick introduction of SQL

  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  DML - Data Manipulation Language  DCL - Data Control Language 3 SQL Commands

 4 DDL - Data Definition Language

 5 DML - Data Manipulation Language

 DCL - Data Control Language 6

 Characteristics Of SQL Commands Here you can see that SQL commands follow a number of basic rules:  SQL keywords are not normally case sensitive, though this in this tutorial all commands (SELECT, UPDATE etc) are upper- cased.  Variable and parameter names are displayed here as lower- case.  New-line characters are ignored in SQL, so a command may be all on one line or broken up across a number of lines for the sake of clarity.  Many DBMS systems expect to have SQL commands terminated with a semicolon character. 7 SQL - Syntax

 create database  The SQL CREATE DATABASE statement is used to create a new SQL database. Syntax  The basic syntax of this CREATE DATABASE statement is as follows :- CREATE DATABASE DatabaseName;  Always the database name should be unique within the RDBMS. Example  If you want to create a new database <studentDB>, then the CREATE DATABASE statement would be as shown below :- CREATE DATABASE studentDB;  Make sure you have the admin privilege before creating any database 9 create database and table

 Steps in Table Creation 1. Identify data types for attributes 2. Identify columns that can and cannot be null 3. Identify columns that must be unique (candidate keys) 4. Identify primary key–foreign key mates 5. Determine default values 6. Identify constraints on columns (domain specifications) 7. Create the table and associated indexes 10 create table

 Create table 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 The basic syntax of the CREATE TABLE statement is as follows :- CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); 11 create database and table

  CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example. A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. 12 create table(contd.)

 Example The following code block is an example, which creates a CUSTOMERS table with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table :- CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );  You can verify if your table has been created successfully by looking at the message displayed by the SQL server 13 create table(contd.)

  The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table. Syntax  The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows. ALTER TABLE table_name ADD column_name datatype;  The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows. ALTER TABLE table_name DROP COLUMN column_name;  The basic syntax of an ALTER TABLE command to change the DATA TYPE of a column in a table is as follows. ALTER TABLE table_name MODIFY COLUMN column_name datatype; 14 Change table(new column, update column)

  The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL;  The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows. ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);  The basic syntax of an ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows. ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);  The basic syntax of an ALTER TABLE command to ADD PRIMARY KEY constraint to atable is as follows. ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...); 15 Change table(new column, update column)

  The basic syntax of an ALTER TABLE command to ADD PRIMARY KEY constraint to atable is as follows. ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);  The basic syntax of an ALTER TABLE command to DROP CONSTRAINT from a table is as follows. ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;  The basic syntax of an ALTER TABLE command to DROP PRIMARY KEY constraint from a table is as follows. ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey; 16 Create a primary key

  The SQL INSERT INTO Statement is used to add new rows of data to a table in the database. Syntax  There are two basic syntaxes of the INSERT INTO statement which are shown below.  INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)VALUES (value1, value2, value3,...valueN);  Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data.  You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. 17 Insert data into table

  The SQL INSERT INTO syntax will be as follows INSERT INTO TABLE_NAME VALUES(value1,value2,value3,...valueN); Example  The following statements would create two records in the CUSTOMERS table.  INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  You can create a record in the CUSTOMERS table by using the second syntax as shown below.  INSERT INTO CUSTOMERS VALUES (3, 'Muffy', 24, 'Indore', 10000.00 ); 18 Insert (contd.)

 Select database  When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.  The SQL USE statement is used to select any existing database in the SQL schema. Syntax  The basic syntax of the USE statement is as shown below :- USE DatabaseName;  Always the database name should be unique within the RDBMS. Example Use studentDB; to select studentDB. 19 select database

  The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets. Syntax  The basic syntax of the SELECT statement is as follows :- SELECT column1, column2, columnN FROM table_name;  Here, column1, column2... are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax.  SELECT * FROM table_name; 20 SELECT Query

 Example Consider the CUSTOMERS table having the following records − +----+----------+-----+-----------+----------+-----------------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+----------------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00| +----+----------+-----+-----------+----------+---------------+ 21 SELECT Query

  If you want to fetch all the fields of the CUSTOMERS table, then you should use the following query. SELECT * FROM CUSTOMERS;  This would produce the result as shown below. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ 22 SELECT Query

  The SQL clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.  The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent sections. 23 where clauses

 Syntax  The basic syntax of the SELECT statement with the WHERE clause is as shown below.  SELECT column1, column2, columnN FROM table_name WHERE [condition]  You can specify a condition using the comparison or logical operators like >, <, =, LIKE,  NOT, etc. The following examples would make this concept clear. Example  Consider the CUSTOMERS table having the following records above 24 where clauses

  The following code is an example which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000  SQL> SELECT ID, NAME, SALARY FROM CUSTOMERSWHERE SALARY > 2000;  This would produce the following result +----+----------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 4 | Chaitali | 6500.00 | | 5 | Hardik | 8500.00 | | 6 | Komal | 4500.00 | | 7 | Muffy | 10000.00 | +----+----------+----------+ 25 where clauses

  The following query is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table for a customer with the name Hardik.  Here, it is important to note that all the strings should be given inside single quotes ('').Whereas, numeric values should be given without any quote as in the above example. SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE NAME = 'Hardik'; This would produce the following result +----+----------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 5 | Hardik | 8500.00 | +----+----------+----------+ 26 where clauses

  The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected. Syntax  The basic syntax of the UPDATE query with a WHERE clause is as follows :- UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];  You can combine N number of conditions using the AND or the OR operators.  The following query will update the ADDRESS for a customer whose ID number is 6 in the customer table. UPDATE CUSTOMERS SET ADDRESS = 'Pune‘ WHERE ID = 6; 27 Update query

  If you want to modify all the ADDRESS and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough as shown in the following code block. UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00; 28 Update query

  The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted. Syntax  The basic syntax of the DELETE query with the WHERE clause is as follows :- DELETE FROM table_name WHERE [condition];  You can combine N number of conditions using AND or OR operators.  The following code has a query, which will DELETE a customer, whose ID is 6. DELETE FROM CUSTOMERS WHERE ID = 6;  If you want to DELETE all the records from the CUSTOMERS table, you do not need to use the WHERE clause and the DELETE query would be as follows − DELETE FROM CUSTOMERS; Now, the CUSTOMERS table would not have any record. 29 Delete query

  Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.  An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.  Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.  Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index. 30 create an index

  The basic syntax of a CREATE INDEX is as follows. CREATE INDEX index_name ON table_name; Single-Column Indexes  A single-column index is created based on only one table column. The basic syntax is as follows. CREATE INDEX index_name ON table_name (column_name);  Unique Indexes  Unique indexes are used not only for performance, but also for data integrity. A unique  index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows. CREATE UNIQUE INDEX index_name on table_name (column_name); 31 The CREATE INDEX Command

  A composite index is an index on two or more columns of a table. Its basic syntax is as follows. CREATE INDEX index_name on table_name (column1, column2);  Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions. The DROP INDEX Command  An index can be dropped using SQL DROP command. Care should be taken when dropping an index because the performance may either slow down or improve.  The basic syntax is as follows :- DROP INDEX index_name; 32 Composite Indexes

  The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT tatement and precedes the ORDER BY clause. Syntax  The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used. SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2 33 SQL GROUP BY

chapter 8 SQL.ppt

More Related Content

Similar to chapter 8 SQL.ppt

Recently uploaded

chapter 8 SQL.ppt