Unit-4.pptx

4040233101 Database Management Syatem Unit No 4: SQL Concepts SEMESTER: 1 PREPARED BY: Prof. Nidhi Chhangani  CHAPTER TOPIC 1 INTRODUCTION  SQL SQL is a database computer language designed for the retrieval and m...

4040233101 Database Management Syatem Unit No 4: SQL Concepts SEMESTER: 1 PREPARED BY: Prof. Nidhi Chhangani  CHAPTER TOPIC 1 INTRODUCTION  SQL SQL is a database computer language designed for the retrieval and management of data in a relational databases like MySQL, MS Access, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres etc. SQL stands for Structured Query Language. SQL was developed in the 1970s by IBM Computer Scientists. SQL (Structured Query Language) is a MUST for the students and working professionals to become a great Software Engineer specially when they are working in Software Development Domain. SQL is the most common language used almost in every application software including banking, finance, education, security etc. to store and manipulate data. All the Relational Database Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, software industry is using different dialects of SQL, such as − MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version of SQL is called JET SQL (native format) etc.  Applications of SQL SQL is one of the most widely used Query Language over the databases. SQL provides following functionality to the database programmers − Execute different database queries against a database. Define the data in a database and manipulate that data. Create data in a relational database management system. Access data from the relational database management system. Create and drop databases and tables. Create and maintain database users. Create view, stored procedure, functions in a database. Set permissions on tables, procedures and views.  Structured Query Language(SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like CREATE, DROP, INSERT, etc. It is also used to perform specific tasks, functions, and queries of data. SQL can perform various tasks like creating a table, adding data to tables, dropping the table, modifying the table, set permission for users. These SQL commands are mainly categorized into five categories: 1. DDL – Data Definition Language 2. DQL – Data Query Language 3. DML – Data Manipulation Language 4. DCL – Data Control Language 5. TCL – Transaction Control Language  DDL (Data Definition Language) DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application. List of DDL commands: CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers). DROP: This command is used to delete objects from the database. ALTER: This is used to alter the structure of the database. TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed. COMMENT: This is used to add comments to the data dictionary. RENAME: This is used to rename an object existing in the database.  Create database The CREATE DATABASE statement is used to create a new SQL database. CREATE DATABASE databasename; E.x. CREATE DATABASE testDB; The DROP DATABASE statement is used to drop an existing SQL database. DROP DATABASE databasename; E.x. DROP DATABASE testDB;  Creating (Declaring) a Relation create table "tablename" ("column1" "data type", "column2" "data type", "column3" "data type",... "column_N" "data type"); Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table. CREATE TABLE STUDENTS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), PRIMARY KEY (ID) );  Drop table The DROP TABLE statement is used to drop an existing table in a database. DROP TABLE table_name; E.x. DROP TABLE Shippers;  SQL INSERT Statement The SQL INSERT statement is used to insert a single or multiple data in a table. In SQL, You can insert the data in two ways: 1. Without specifying column name Syntax INSERT INTO TABLE_NAME VALUES (value1, value2, value 3,.... Value N); Query INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48); 2. By specifying column name To insert partial column values, you must have to specify the column names. Syntax INSERT INTO TABLE_NAME [(col1, col2, col3,.... col N)] VALUES (value1, value2, value 3,.... Value N); Querythe INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);  SQL Update Statement The SQL UPDATE statement is used to modify the data that is already in the database. The condition in the WHERE clause decides that which row is to be updated. Syntax UPDATE table_name SET column1 = value1, column2 = value2,... WHER E condition; QUERY 1. UPDATE EMPLOYEE SET EMP_NAME = ’TIYA' WHERE SALARY = 5000 00;  SQL DELETE Statement The SQL DELETE statement is used to delete rows from a table. Generally, DELETE statement removes one or more records form a table. Syntax 1. DELETE FROM table_name WHERE some_condition; QUERY: DELETE FROM EMPLOYEE WHERE EMP_NAME = ’XYZ';  SQL Constraints SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. The following constraints are commonly used in SQL: NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY- A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Prevents actions that would destroy links between tables CHECK - Ensures that the values in a column satisfies a specific condition DEFAULT - Sets a default value for a column if no value is specified CREATE INDEX - Used to create and retrieve data from the database very quickly  Primary Key A column or columns is called primary key (PK) that uniquely identifies each row in the table. If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table. When multiple columns are used as a primary key, it is known as composite primary key. SQL primary key for one column: The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created. CREATE TABLE students ( S_Id int NOT NULL PRIMARY KEY, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255), )  FOREIGN KEY SQL FOREIGN KEY In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables. In simple words you can say that, a foreign key in one table used to point primary key in another table. Let us take an example to explain it:S_Id LastName FirstName CITY 1 MAURYA AJEET ALLAHABAD 2 JAISWAL RATAN GHAZIABAD 3 ARORA SAUMYA MODINAGAR O_Id OrderNo S_Id 1 99586465 2 2 78466588 2 3 22354846 3 4 57698656 1 The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table. The "S_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table. The foreign key constraint is generally prevents action that destroy links between tables. It also prevents invalid data to enter in foreign key column. CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, Order_No int NOT NULL, S_Id int FOREIGN KEY REFERENCES persons (S_Id) )  Delete & Alter table operation The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition. 1. DELETE FROM table_name; 2. DELETE FROM table_name [WHERE condition]; The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. This statement also allows database users to add and remove various SQL constraints on the existing tables. e.x.: ALTER TABLE Employee ADD ( Emp_ContactNo. Number(13), Emp_EmailID varch ar(50) ; 1.ALTER TABLE Employee ADD ( Emp_ContactNo. Numb er(13), Emp_EmailID varchar(50) ;  SQL The SELECT Statement The SELECT statement is used to select data from a table. The tabular result is stored in a result table (called the result-set). Syntax SELECT column_name(s) FROM table_name To select the columns named "LastName" and "FirstName", use a SELECT statement like this: SELECT LastName, FirstName FROM Persons Persons LastName FirstName Address City Hansen Ola Timoteivn 10 Sandnes Svendson Tove Borgvn 23 Sandnes Pettersen Kari Storgt 20 Stavanger výsledo k LastName FirstName Hansen Ola Svendson Tove Pettersen Kari  Select All Columns To select all columns from the "Persons" table, use a * symbol instead of column names, like this: SELECT * FROM Persons LastName FirstName Address City Hansen Ola Timoteivn 10 Sandnes Svendson Tove Borgvn 23 Sandnes Pettersen Kari Storgt 20 Stavanger  The Result Set The result from a SQL query is stored in a result-set. Most database software systems allow navigation of the result set with programming functions, like: Move-To-First-Record, Get-Record- Content, Move-To-Next-Record, etc. Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our ADO tutorial.  Semicolon after SQL Statements? Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Some SQL tutorials end each SQL statement with a semicolon. Is this necessary? We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.  The SELECT DISTINCT Statement The DISTINCT keyword is used to return only distinct (different) values. The SELECT statement returns information from table columns. But what if we only want to select distinct elements? With SQL, all we need to do is to add a DISTINCT keyword to the SELECT statement: Syntax SELECT DISTINCT column_name(s) FROM table_name  Using the DISTINCT keyword To select ALL values from the column named "Company" we use a SELECT statement like this: SELECT Company FROM Orders Orders Company Sega Company OrderNumber W3Schools Sega 3412 Trio W3Schools 2312 W3Schools Trio 4678 W3Schools 6798 Note that "W3Schools" is listed twice in the result-set. To select only DIFFERENT values from the column named "Company" we use a SELECT DISTINCT statement like this: SELECT DISTINCT Company FROM Orders Orders Company Sega Company OrderNumber W3Schools Sega 3412 Trio W3Schools 2312 Trio 4678 W3Schools 6798  Select All Columns The WHERE clause is used to specify a selection criterion. The WHERE Clause To conditionally select data from a table, a WHERE clause can be added to the SELECT statement. Syntax SELECT column FROM table WHERE column operator value With the WHERE clause, the following operators can be used: Operator Description = Equal Not equal > Greater than < Less than >= Greater than or equal Greater than < Less than >= Greater than or equal =20; 3. SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST;  SUM() Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. Syntax SUM() or SUM( [ALL|DISTINCT] expression ) Example: SUM() SELECT SUM(COST) FROM PRODUCT_MAST;  AVG() The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values. Syntax AVG() or AVG( [ALL|DISTINCT] expression ) Example: SELECT AVG(COST) FROM PRODUCT_MAST;  MAX() MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column. Syntax MAX() or MAX( [ALL|DISTINCT] expression ) Example: 1. SELECT MAX(RATE) FROM PRODUCT_MAST;  MIN () MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column. Syntax MIN() or MIN( [ALL|DISTINCT] expression ) Example: SELECT MIN(RATE) FROM PRODUCT_MAST;  Views in SQL() Views in SQL are considered as a virtual table. A view also contains rows and columns. To create the view, we can select the fields from one or more tables present in the database. A view can either have specific rows based on certain condition or all the rows of a table. Creating view A view can be created using the CREATE VIEW statement. We can create a view from a single table or multiple tables. Syntax: CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE condition;  2. Creating View from a single table In this example, we create a View named DetailsView from the table Student_Detail. Query: CREATE VIEW Details_View AS SELECT NAME, ADDRESS FROM Student_Details WHERE STU_ID < 4; Just like table query, we can query the view to view the data. SELECT * FROM DetailsView;