SQL OVERVIEW for a new introduced student.pptx

This explains the SQL commands

1. SQL OVERVIEW

2. SQL The name SQL pronounced as “ ess -cue-ell” or ‘sequel’ is the abbreviation for structured query language. The SQL consists of a set of facilities for defining, accessing and managing relational databases. All tasks related to relational data management-creating tables, querying the database, deleting, granting access to users etc., can be done using SQL. It has been accepted as an American standard by American National Standards Institute (ANSI) and is a Federal Information Processing Standard (FIPS). It is also an international standard recognized by the ISO. The first commercial DBMS that supported SQL was Oracle in 1979. SQL statements can be invoked either interactively in a terminal session or by embedding them in application programs.

3. Characteristics of SQL The following are the important characteristics of SQL. 1. SQL is extremely flexible. 2. SQL uses a free form syntax that gives the user the ability to structure SQL statements in a way best suited. 3. It is a free formatted language, i.e., there is no need to start SQL statements in a particular column or to be finished in a single line. 4. It has relatively few commands. 5. It is a non-procedural language.

4. Advantages of SQL The advantages of SQL are as follows: 1. SQL is a high level language that provides a greater degree of abstraction than procedural languages. The programmer has to specify what data is needed but need not to specify, how to retrieve it. 2. SQL is a unified language. The same language can be used to define data structures, querying data, control access to the data, insert, delete and modify occurrences of the data and so on. 3. All the programs written in SQL are portable, thus they can be moved from one database to another with very little modification. Such porting could be required when DBMS needs to be upgraded or changed. 4. The language is simple and easy to learn. It can handle complex situations very efficiently.

5. Advantages of SQL 4. The language is simple and easy to learn. It can handle complex situations very efficiently. 5. The language has sound theoretical base and there is no ambiguity about the way a query will interpret the data and produce the results. Thus the results to be expected are well defined. 6. SQL processes sets-of-records rather than just one record-at-a time. This set-at-a time feature of the SQL makes it more powerful. 7. SQL as a language is independent of the way it is implemented internally. This is because SQL specifies what is required and not how it should be done. 8. SQL enables its users to deal with a number of database management systems where it is available.

6. Sql environment

7. Sql language types Data definition language (DDL) Commands used to define a database, including those for creating, altering, and dropping tables and establishing constraints. Data manipulation language (DML) Commands used to maintain and query a database, including those for updating, inserting, modifying, and querying data. Data control language (DCL) Commands used to control a database, including those for administering privileges and committing (saving) data.

8. Language types

9. Parts (Components) of SQL Language Query Dispatcher Optimization Engines Classic Query Engine SQL Query Engine, 

10. SQL Data Types Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column. Data types mainly classified into three categories for every database. String Data types Numeric Data types Date and time Data types research on specific examples on each category.

11. SQL Operators The SQL reserved words and characters are called operators, which are used with a WHERE clause in a SQL query. In SQL, an operator can either be a unary or binary operator. The unary operator uses only one operand for performing the unary operation, whereas the binary operator uses two operands for performing the binary operation. Syntax Operator  SQL_Operand   (Unary operator) Operand1  SQL_Operator  Operand2   (Binary Operator)

12. Operators

13. Types of Operator SQL Arithmetic Operators SQL Comparison Operators SQL Logical Operators SQL Set Operators SQL Bit-wise Operators SQL Unary Operators

14. Sql syntax Syntax Guidelines You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase improves the readability of the SQL query. SQL statements or syntax are dependent on text lines. We can place a single SQL statement on one or multiple text lines. You can perform most of the action in a database with SQL statements. SQL syntax depends on relational algebra and tuple relational calculus.

15. Sql statements SQL statements tell the database what operation you want to perform on the structured data and what information you would like to access from the database. Each SQL statement begins with any of the SQL keywords and ends with the semicolon (;). The semicolon is used in the SQL for separating the multiple Sql statements which are going to execute in the same call. Example: SELECT  " column_name "  FROM  " table_name ";    

16. Ddl statements Create: ( Database, table) Drop: ( Database, table) Alter: ( Database, table)

17. Create: ( Database) In SQL, the 'Create Database' statement is a first step for storing the structured data in the database. Sytanx CREATE   DATABASE   Database_Name ;  In this syntax,  Database_Name  specifies the name of the database which we want to create in the system. We have to type the database name in query just after the 'Create Database' keyword. CREATE   DATABASE  Student ;   This example creates the  Student  database. To create the Student database, you have to type the following command in Structured Query Language:

18. SQL DROP Database In this SQL syntax, we have to specify the name of that database which we want to delete permanently from the database system. We have to write the name of the database after the DROP DATABASE keyword in every example. DROP   DATABASE  Database_Name1, [ Database_Name2, ......., Database_NameN ] ;  DROP   DATABASE  Student;  

19. SQL RENAME TABLE In some situations, database administrators and users want to change the name of the table in the SQL database because they want to give a more relevant name to the table. RENAME  old_table  _name To  new_table_name  ;  RENAME Cars To Car_2021_Details ;   

20. DML: Select statement The SELECT statement is the most commonly used command in Structured Query Language. It is used to access the records from one or more database tables and views. It also retrieves the selected data that follow the conditions we want. Syntax of SELECT Statement in SQL SELECT  Column_Name_1, Column_Name_2, .....,  Column_Name_N   FROM   Table_Name ; In this SELECT syntax,  Column_Name_1, Column_Name_2, ….., Column_Name_N  are the name of those columns in the table whose data we want to read. If you want to access all rows from all fields of the table, use the following SQL SELECT syntax with * asterisk sign

21. Select statement Examples of SELECT Statement in SQL Here, we took the following two different SQL examples which will help you to execute the SELECT statement for retrieving the records: CREATE   TABLE   Student_Records     (   Student_Id   Int   PRIMARY   KEY ,     First_Name   VARCHAR  (20),     Address  VARCHAR  (20),     Age  Int  NOT NULL,   Percentage  Int  NOT NULL,   Grade  VARCHAR  (10)    ) ; 

22. The following query inserts the record of intelligent students into the  Student_Records  table: INSERT   INTO  Student  VALUES  (201,  Jina ,  lilongwe , 18, 89, A),    (202,  Bado ,  Karonga , 19, 93, A),   (203,  Yash , Mzuzu, 20, 89, A),     (204,  Bhima , Blantyre, 19, 78, B),   (05,  Yasini ,  Mangich , 20, 75, B),   (206,  Letina , Bale, 19, 51, C),   (207, Vivian,  Neno , 20, 62, B);  

23. The following SQL query displays all the values of each column from the above Student_records table: SELECT  *  FROM   Student_Records ;   The following query displays the values of particular column from the above  Student_Record  table: SELECT   Student_Id , Age, Percentage, Grade  FROM   Student_Records ;  

24. SQL Clause Clauses are  in-built functions available to us in SQL . With the help of clauses, we can deal with data easily stored in the table. Clauses help us filter and analyze data quickly. When we have large amounts of data stored in the database, we use Clauses to query and get data required by the user

25. SQL WHERE A  WHERE clause  in SQL is a data manipulation language statement. WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query. Actually. it filters the records. It returns only those queries which fulfill the specific conditions. WHERE clause is used in SELECT, UPDATE, DELETE statement etc.

26. syntax SELECT  column1,  column  2, ...  column  n   FROM      table_name    WHERE  [conditions]   Column Operators : < , >, >=, <=, !=, <>, BETWEEN, IN, LIKE Example SELECT First_Name , age FROM Student_Records WHERE grade >= 60; SELECT  *  FROM   Employee_Details   WHERE   Emp_Panelty  = 500; 

27. HAVING Clause The HAVING clause places the condition in the groups defined by the GROUP BY clause in the SELECT statement. This SQL clause is implemented after the 'GROUP BY' clause in the 'SELECT' statement. This clause is used in SQL because we cannot use the WHERE clause with the SQL aggregate functions. Both WHERE and HAVING clauses are used for filtering the records in SQL queries.

28. Syntax SELECT  column_Name1, column_Name2, .....,  column_NameN   aggregate_function_name ( column_Name )  FROM   table_name   GROUP   BY  column_Name1  HAVING  condition;   Examples  Example 1:  Let's take the following  Employee  table, which helps you to analyze the HAVING clause with SUM aggregate function:

29. Employee table Emp_Id Emp_Name Emp_Salary Emp_City 201 Bhahat 2000 Mzuzu 202 Nikita 4000 Lilongwe 203 Thom 8000 Blantyre 204 Banda 2000 Mzuzu 205 Phiri 5000 Lilongwe

30. example SELECT  SUM( Emp_Salary ),  Emp_City   FROM  Employee  GROUP   BY   Emp_City ;  SUM( Emp_Salary ) Emp_City 4000 Mzuzu 9000 Lilongwe 8000 Blantyre

31. example Now, suppose that you want to show those cities whose total salary of employees is more than 5000. For this case, you have to type the following query with the HAVING clause in SQL: SELECT  SUM( Emp_Salary ),  Emp_City   FROM  Employee  GROUP   BY   Emp_City   HAVING  SUM( Emp_Salary )>5000; Output?

32.  ORDER BY Clause Whenever we want to sort the records based on the columns stored in the tables of the SQL database, then we consider using the ORDER BY clause in SQL. The ORDER BY clause in SQL will help us to sort the records based on the specific column of a table. This means that all the values stored in the column on which we are applying ORDER BY clause will be sorted, and the corresponding column values will be displayed in the sequence in which we have obtained the values in the earlier step. Using the ORDER BY clause, we can sort the records in ascending or descending order as per our requirement. The records will be sorted in ascending order whenever the  ASC keyword  is used with ORDER by clause.  DESC keyword  will sort the records in descending order.

33. syntax Syntax to sort the records in ascending order: SELECT  ColumnName1,..., ColumnNameN   FROM   TableName    ORDER   BY   ColumnName   ASC ;     Syntax to sort the records in descending order: SELECT  ColumnName1,..., ColumnNameN   FROM   TableName    ORDER   BY   ColumnNameDESC ;     Syntax to sort the records in ascending order without using ASC keyword: SELECT  ColumnName1,..., ColumnNameN   FROM   TableName    ORDER   BY   ColumnName ;

34. Sql join As the name shows, JOIN means  to combine something . In case of SQL, JOIN means  "to combine two or more tables" . The SQL JOIN clause takes records from two or more tables in a database and combines it together. inner join, left outer join, right outer join, full outer join, cross join.

35. Join use If you want to access more than one table through a select statement. If you want to combine two or more table then SQL JOIN statement is used . It combines rows of that tables in one table and one can retrieve the information by a SELECT statement. The joining of two or more tables is based on common field between them. SQL INNER JOIN also known as simple join is the most common type of join.

36. INNER JOIN SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;   table1: First table. table2: Second table matching_column : Column common to both the tables.