DBMS information in detail || Dbms (lab) ppt

DBMS information in detail || Dbms (lab) ppt

1. DBMS (LAB) SQL/PLSQL By:-Gourav Kottawar 1

2. Introduction of SQL DDL, DML, DTL Basic Data Types Char, varchar /varchar2, long, number, Fixed & floating point Date, CLOB, BLOB By:-Gourav Kottawar 2

3. What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL is an ANSI (American National Standards Institute) standard What Can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views By:-Gourav Kottawar 3

4. SELECT * FROM Persons; SQL is not case sensitive , Semicolon after SQL Statements. Some database systems require a semicolon at the end of each SQL statement. 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. 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. By:-Gourav Kottawar 4

5. SQL DML and DDL SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL). The query and update commands form the DML part of SQL: SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database By:-Gourav Kottawar 5

6. The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are: CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table By:-Gourav Kottawar 6

7. A Transaction Control Language ( TCL ) : is a computer language and a subset of SQL , used to control transactional processing in a database. A transaction is logical unit of work that comprises one or more SQL statements, usually a group of Data Manipulation Language (DML) statements. Examples of TCL commands include: COMMIT to apply the transaction by saving the database changes. ROLLBACK to undo all changes of a transaction. SAVEPOINT to divide the transaction into smaller sections. It defines breakpoints for a transaction to allow partial rollbacks. By:-Gourav Kottawar 7

8. DATA TYPES IN SQL By:-Gourav Kottawar 8

9. By:-Gourav Kottawar 9 Data Type Syntax Explanation (if applicable) INTEGER INTEGER(p) Integer numerical (no decimal). Precision p SMALLINT Integer numerical (no decimal). Precision 5 INTEGER Integer numerical (no decimal). Precision 10

10. By:-Gourav Kottawar 10 Syntax float [ ( n ) ] Is a floating point number data with the following valid values: - 1.79E + 308 through -2.23E - 308, 0 and 2.23E -308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53 . The default value of n is 53 . real Is a floating point number data with the following valid values: –3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24) .

11. nvalue Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes By:-Gourav Kottawar 11 Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes

12. character char(x) Where x is the number of characters to store. This data type is space padded to fill the number of characters specified. character varying varchar2(x) Where x is the number of characters to store. This data type does NOT space pad. BOOLEAN BOOLEAN Stores TRUE or FALSE values date date Stores year, month, and day values. time time Stores the hour, minute, and second values. timestamp timestamp Stores year, month, day, hour, minute, and second values. By:-Gourav Kottawar 12

13. Difference between VARCHAR and VARCHAR2 VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype . 2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. 3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space. By:-Gourav Kottawar 13

14. Difference Between Number and Integer NUMBER always stores as we entered. Scale is -84 to 127. But INTEGER rounds to whole number. The scale for INTEGER is 0. INTEGER is equivalent to NUMBER(38,0). It means, INTEGER is constrained number. The decimal place will be rounded. But NUMBER is not constrained. INTEGER(12,2) => 12 INTEGER(12.5) => 13 INTEGER(12.9) => 13 INTEGER(12.4) => 12 NUMBER(12,2) => 12.2 NUMBER(12.5) => 12.5 NUMBER(12.9) => 12.9 NUMBER(12.4) => 12.4 By:-Gourav Kottawar 14

15. Database Tables A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data. Below is an example of a table called "Persons": By:-Gourav Kottawar 15

16. By:-Gourav Kottawar 16 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger The table above contains three records (one for each person) and five columns ( P_Id , LastName , FirstName , Address, and City).

17. The CREATE TABLE Statement The CREATE TABLE statement is used to create a table in a database. SQL CREATE TABLE Syntax CREATE TABLE table_name ( column_name1 data_type , column_name2 data_type , column_name3 data_type , .... ) By:-Gourav Kottawar 17

18. CREATE TABLE Example Now we want to create a table called "Persons" that contains five columns: P_Id , LastName , FirstName , Address, and City. We use the following CREATE TABLE statement: CREATE TABLE Persons ( P_Id int , LastName varchar (255), FirstName varchar (255), Address varchar (255), City varchar (255) ); By:-Gourav Kottawar 18

19. The P_Id column is of type int and will hold a number. The LastName , FirstName , Address, and City columns are of type varchar with a maximum length of 255 characters. The empty "Persons" table will now look like this: The empty table can be filled with data with the INSERT INTO statement. By:-Gourav Kottawar 19 P_Id LastName FirstName Address City          

20. SQL Constraints Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). We will focus on the following constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT By:-Gourav Kottawar 20

21. SQL NOT NULL Constraint The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. The following SQL enforces the " P_Id " column and the " LastName " column to not accept NULL values: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255) ) By:-Gourav Kottawar 21

22. SQL UNIQUE Constraint The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. By:-Gourav Kottawar 22

23. SQL UNIQUE Constraint on CREATE TABLE The following SQL creates a UNIQUE constraint on the " P_Id " column when the "Persons" table is created: MySQL : CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255), UNIQUE ( P_Id ) ) By:-Gourav Kottawar 23

24. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255) ) By:-Gourav Kottawar 24

25. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255) ) To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255), CONSTRAINT uc_PersonsId UNIQUE ( P_Id,LastName ) ) By:-Gourav Kottawar 25

26. SQL UNIQUE Constraint on ALTER TABLE To create a UNIQUE constraint on the " P_Id " column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD UNIQUE ( P_Id ) To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE ( P_Id,LastName ) By:-Gourav Kottawar 26

27. To DROP a UNIQUE Constraint To drop a UNIQUE constraint, use the following SQL: SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT uc_PersonID By:-Gourav Kottawar 27

28. SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key. SQL PRIMARY KEY Constraint on CREATE TABLE By:-Gourav Kottawar 28

29. The following SQL creates a PRIMARY KEY on the " P_Id " column when the "Persons" table is created: MySQL : CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255), PRIMARY KEY ( P_Id ) ) SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255) ) By:-Gourav Kottawar 29

30. SQL PRIMARY KEY Constraint on ALTER TABLE To create a PRIMARY KEY constraint on the " P_Id " column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD PRIMARY KEY ( P_Id ) By:-Gourav Kottawar 30

31. MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY ( P_Id ); Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created). By:-Gourav Kottawar 31

32. To DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL: MySQL : ALTER TABLE Persons DROP PRIMARY KEY SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT pk_PersonID By:-Gourav Kottawar 32

33. The FOREIGN KEY constraint SQL FOREIGN KEY Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let's illustrate the foreign key with an example. Look at the following two tables: The "Persons" table: By:-Gourav Kottawar 33

34. P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger By:-Gourav Kottawar 34 O_Id OrderNo P_Id 1 77895 3 2 44678 3 3 22456 2 4 24562 1 PERSONS TABLE ORDERS TABLE

35. 35 Note that the " P_Id " column in the "Orders" table points to the " P_Id " column in the "Persons" table. The " P_Id " column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The " P_Id " column in the "Orders" table is a FOREIGN KEY in the "Orders" table. The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables. The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to . By:-Gourav Kottawar

36. CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int , PRIMARY KEY ( O_Id ), FOREIGN KEY ( P_Id ) REFERENCES Persons( P_Id ) ) By:-Gourav Kottawar 36

37. CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons( P_Id ) ) By:-Gourav Kottawar 37

38. DROP TABLE Helps in removing the rows from the table. It is a DML command. The description (structure) of the table is removed. The existence of the table is removed. (Table cant be seen from in select * from tab). Rollback though shows completed but table view doesn’t come back. AS IT IS A DML COMMAND ROLLBACK WILL NOT WORK By:-Gourav Kottawar 38

39. ALTER table tablename DROP column colname SQL> alter table emp 2 drop column age; drop column age * ERROR at line 2: ORA-00905: missing keyword ALTER-Drop column will work in Oracle 9i and above version. By:-Gourav Kottawar 39

40. ALTER TABLE Orders ADD FOREIGN KEY ( P_Id ) REFERENCES Persons( P_Id ); ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY ( P_Id ) REFERENCES Persons( P_Id ) By:-Gourav Kottawar 40

41. SQL> drop table room; Table dropped. SQL> desc room; //description is not available ERROR: ORA-04043: object room does not exist By:-Gourav Kottawar 41

42. The INSERT INTO Statement The INSERT INTO statement is used to insert a new row in a table. SQL INSERT INTO Syntax: It is possible to write the INSERT INTO statement in two forms. The first form doesn't specify the column names where the data will be inserted, only their values: INSERT INTO table_name VALUES (value1, value2, value3,...) By:-Gourav Kottawar 42

43. INSERT INTO Persons ( P_Id , LastName , FirstName ) VALUES (5, ' Tjessem ', ' Jakob ') The second form specifies both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) By:-Gourav Kottawar 43

44. SQL INSERT INTO Example We have the following "Persons" table: By:-Gourav Kottawar 44 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger : Now we want to insert a new row in the "Persons" table. We use the following SQL statement INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', ' Bakken 2', 'Stavanger') P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 4 Nilsen Johan Bakken 2 Stavanger

45. Insert Data Only in Specified Columns It is also possible to only add data in specific columns. The following SQL statement will add a new row, but only add data in the " P_Id ", " LastName " and the " FirstName " columns: INSERT INTO Persons ( P_Id , LastName , FirstName ) VALUES (5, ' Tjessem ', ' Jakob ') The "Persons" table will now look like this: By:-Gourav Kottawar 45 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 4 Nilsen Johan Bakken 2 Stavanger 5 Tjessem Jakob    

46. The SQL SELECT Statement The SELECT statement is used to select data from a database . The result is stored in a result table, called the result-set . SQL SELECT Syntax SELECT column_name (s) FROM table_name SELECT * FROM table_name And ‘*’ Also called as Global Extraction Opearator Note: SQL is not case sensitive. SELECT is the same as select. An SQL SELECT Example The "Persons" table: By:-Gourav Kottawar 46

47. P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger By:-Gourav Kottawar 47

48. SQL CHECK Constraint on CREATE TABLE The following SQL creates a CHECK constraint on the " P_Id " column when the "Persons" table is created. The CHECK constraint specifies that the column " P_Id " must only include integers greater than 0. CREATE TABLE Persons ( P_Id int NOT NULL CHECK ( P_Id >0), LastName varchar (25) NOT NULL, FirstName varchar (25), Address varchar (25), City varchar (25) ) By:-Gourav Kottawar 48

49. To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255), CONSTRAINT chk_Person CHECK ( P_Id >0 AND City=' Sandnes ') ) By:-Gourav Kottawar 49

50. SQL CHECK Constraint on ALTER TABLE To create a CHECK constraint on the " P_Id " column when the table is already created, use the following SQL: ALTER TABLE Persons ADD CHECK ( P_Id >0) To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK ( P_Id >0 AND City=' Sandnes ') By:-Gourav Kottawar 50

51. To DROP a CHECK Constraint To drop a CHECK constraint, use the following SQL: ALTER TABLE Persons DROP CONSTRAINT chk_Person ; By:-Gourav Kottawar 51

52. The DROP TABLE Statement The DROP TABLE statement is used to delete a table. DROP TABLE table_name By:-Gourav Kottawar 52

53. SQL SELECT Syntax SELECT column_name(s) FROM table_name SELECT * FROM table_name And ‘*’ Also called as Global Extraction Opearator By:-Gourav Kottawar 53

54. P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger By:-Gourav Kottawar 54

55. SELECT LastName,FirstName FROM Persons The result-set will look like this: By:-Gourav Kottawar LastName FirstName Hansen Ola Svendson Tove Pettersen Kari 55

56. The SQL SELECT DISTINCT Statement In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table. The DISTINCT keyword can be used to return only distinct (different) values. By:-Gourav Kottawar 56

57. SELECT DISTINCT column_name (s) FROM table_name SELECT DISTINCT Example The "Persons" table: SELECT DISTINCT City FROM Persons; The result-set will look like this: City Sandnes Stavanger By:-Gourav Kottawar 57

58. CLAUSES IN SQL The WHERE Clause  SELECT column_name(s) FROM table_name WHERE column_name operator value By:-Gourav Kottawar 58

59. SELECT * FROM Persons WHERE City=' Sandnes ' The result-set will look like this: By:-Gourav Kottawar P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 59

60. This is correct: SELECT * FROM Persons WHERE FirstName='Tove‘ ; This is wrong: SELECT * FROM Persons WHERE FirstName=Tove By:-Gourav Kottawar 60

61. This is correct: SELECT * FROM Persons WHERE Year=1965 This is wrong: SELECT * FROM Persons WHERE Year='1965' By:-Gourav Kottawar 61

62. Operators Allowed in the WHERE Clause With the WHERE clause, the following operators can be used: By:-Gourav Kottawar Operator Description = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns 62

63. Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson": We use the following SELECT statement: SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson‘ ; By:-Gourav Kottawar 63

64. The result-set will look like this: By:-Gourav Kottawar P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes 64

65. Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":We use the following SELECT statement: SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola‘ ; By:-Gourav Kottawar 65

66. P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes By:-Gourav Kottawar 66

67. SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola'); The result-set will look like this: By:-Gourav Kottawar P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes 67

68. SQL IN Operator The IN Operator The IN operator allows you to specify multiple values in a WHERE clause. SQL IN Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) By:-Gourav Kottawar 68

69. IN Operator Example The "Persons" table: We use the following SELECT statement: SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen') The result-set will look like this: By:-Gourav Kottawar 69

70. _Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 3 Pettersen Kari Storgt 20 Stavanger By:-Gourav Kottawar 70

71. SQL BETWEEN Operator The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The BETWEEN Operator The BETWEEN operator selects range of data between two values. The values can be numbers, text, or dates. By:-Gourav Kottawar 71

72. SQL BETWEEN Syntax:SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 By:-Gourav Kottawar 72

73. LIKE Operator Using the % Wildcard 1.Now we want to select the persons living in a city that starts with " sa " from the "Persons" table. We use the following SELECT statement: SELECT * FROM Persons WHERE City LIKE ' sa %' The result-set will look like this: By:-Gourav Kottawar 73 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes

74. 2. Next, we want to select the persons living in a city that contains the pattern " nes " from the "Persons" table. We use the following SELECT statement: SELECT * FROM Persons WHERE City LIKE '% nes %' The result-set will look like this By:-Gourav Kottawar 74 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes

75. 3.Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table. We use the following SELECT statement: SELECT * FROM Persons WHERE FirstName LIKE '_la' The result-set will look like this: By:-Gourav Kottawar 75 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes

76. 4.Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table. We use the following SELECT statement: SELECT * FROM Persons WHERE LastName LIKE ' S_end_on ' The result-set will look like this: By:-Gourav Kottawar 76 _Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes

77. Using the [ charlist ] Wildcard 5.Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table. We use the following SELECT statement: SELECT * FROM Persons WHERE LastName LIKE '[ bsp ]%' The result-set will look like this: By:-Gourav Kottawar 77 P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

78. 5.Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table. We use the following SELECT statement: SELECT * FROM Persons WHERE LastName LIKE '[! bsp ]%' The result-set will look like this: By:-Gourav Kottawar 78 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes

79. The UPDATE Statement The UPDATE statement is used to update existing records in a table. SQL UPDATE Syntax UPDATE table_name SET column1=value, column2=value2,... WHERE some_column = some_value By:-Gourav Kottawar 79

80. Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! SQL UPDATE Example :The "Persons" table By:-Gourav Kottawar 80 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 4 Nilsen Johan Bakken 2 Stavanger 5 Tjessem Jakob    

81. Now we want to update the person " Tjessem , Jakob " in the "Persons" table. We use the following SQL statement: UPDATE Persons SET Address=' Nissestien 67', City=' Sandnes ' WHERE LastName =' Tjessem ' AND FirstName =' Jakob ‘; By:-Gourav Kottawar 81 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 4 Nilsen Johan Bakken 2 Stavanger 5 Tjessem Jakob Nissestien 67 Sandnes

82. The SQL SELECT DISTINCT Statement In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table. The DISTINCT keyword can be used to return only distinct (different) values. SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name (s) FROM table_name SELECT DISTINCT Example The "Persons" table: By:-Gourav Kottawar 82

83. P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger By:-Gourav Kottawar 83

84. CLAUSES IN SQL The WHERE Clause  The WHERE clause is used to extract only those records that fulfill a specified criterion. SQL WHERE Syntax SELECT column_name (s) FROM table_name WHERE column_name operator value By:-Gourav Kottawar 84

85. WHERE Clause Example The "Persons" table: By:-Gourav Kottawar 85 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger SELECT * FROM Persons WHERE City=' Sandnes ' Now we want to select only the persons living in the city " Sandnes " from the table above. We use the following SELECT statement: The result-set will look like this:

86. P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes By:-Gourav Kottawar 86 Quotes Around Text Fields SQL uses single quotes around text values (most database systems will also accept double quotes). Although, numeric values should not be enclosed in quotes.

87. This is correct: SELECT * FROM Persons WHERE FirstName =' Tove ' This is wrong: SELECT * FROM Persons WHERE FirstName = Tove For numeric values: This is correct: SELECT * FROM Persons WHERE Year=1965 This is wrong: SELECT * FROM Persons WHERE Year='1965' By:-Gourav Kottawar 87

88. Operators Allowed in the WHERE Clause With the WHERE clause, the following operators can be used: By:-Gourav Kottawar 88 Operator Description = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns

89. The AND operator displays a record if both the first condition and the second condition is true. The OR operator displays a record if either the first condition or the second condition is true. AND Operator Example The "Persons" table: By:-Gourav Kottawar 89 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

90. Now we want to select only the persons with the first name equal to " Tove " AND the last name equal to " Svendson ": We use the following SELECT statement: SELECT * FROM Persons WHERE FirstName =' Tove ' AND LastName =' Svendson ‘ The result-set will look like this: By:-Gourav Kottawar 90 P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes

91. OR Operator Example Now we want to select only the persons with the first name equal to " Tove " OR the first name equal to "Ola":We use the following SELECT statement: SELECT * FROM Persons WHERE FirstName =' Tove ' OR FirstName ='Ola' By:-Gourav Kottawar 91

92. The result-set will look like this: By:-Gourav Kottawar 92 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes Combining AND & OR You can also combine AND and OR (use parenthesis to form complex expressions). Now we want to select only the persons with the last name equal to " Svendson " AND the first name equal to " Tove " OR to "Ola": We use the following SELECT statement:   SELECT * FROM Persons WHERE LastName =' Svendson ' AND ( FirstName =' Tove ' OR FirstName ='Ola')

93. SQL IN Operator The IN Operator The IN operator allows you to specify multiple values in a WHERE clause. SQL IN Syntax: SELECT column_name (s) FROM table_name WHERE column_name IN (value1,value2,...) By:-Gourav Kottawar 93

94. IN Operator Example Now we want to select the persons with a last name equal to "Hansen" or " Pettersen " from the table above. We use the following SELECT statement: SELECT * FROM Persons WHERE LastName IN (' Hansen','Pettersen ') The result-set will look like this : By:-Gourav Kottawar 94 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

95. SQL BETWEEN Operator The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The BETWEEN Operator The BETWEEN operator selects range of data between two values. The values can be numbers, text, or dates. SQL BETWEEN Syntax:SELECT column_name (s) FROM table_name WHERE column_name BETWEEN value1 AND value2         By:-Gourav Kottawar 95

96. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND ' Pettersen ' The result-set will look like this: By:-Gourav Kottawar 96 P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes To display the persons outside the range in the previous example, use NOT BETWEEN: SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND ' Pettersen ' The result-set will look like this: P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

97. ORDER BY clause So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal. By:-Gourav Kottawar 97

98. The syntax for an ORDER BY statement is as follows: SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY "column_name" [ASC, DESC] By:-Gourav Kottawar 98

99. The [] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC . By:-Gourav Kottawar 99

100. For example, we may wish to list the contents of Table Store_Information by dollar amount, in descending order: By:-Gourav Kottawar store_name Sales Date Los Angeles $1500 Jan-05-1999 Boston $700 Jan-08-1999 San Francisco $300 Jan-08-1999 San Diego $250 Jan-07-1999 100

101. The GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SQL GROUP BY Syntax: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name By:-Gourav Kottawar 101

102. O_Id OrderDate OrderPrice Customer 1 2008/11/12 1000 Hansen 2 2008/10/23 1600 Nilsen 3 2008/09/02 700 Hansen 4 2008/09/03 300 Hansen 5 2008/08/30 2000 Jensen 6 2008/10/04 100 Nilsen By:-Gourav Kottawar 102

103. Now we want to find the total sum (total order) of each customer. We will have to use the GROUP BY statement to group the customers. We use the following SQL statement: SELECT Customer, SUM( OrderPrice ) FROM Orders GROUP BY Customer The result-set will look like this: By:-Gourav Kottawar Customer SUM(OrderPrice) Hansen 2000 Nilsen 1700 Jensen 2000 103

104. We can also use the GROUP BY statement on more than one column, like this: SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate By:-Gourav Kottawar 104

105. Date functions in SQL   1. SYSDATE() Sysdate to diplay system date.   SQL> select sysdate from dual;   SYSDATE --------- 23-NOV-10   By:-Gourav Kottawar 105

106. 2. LAST_DAY: Last_day to display last day of the current/mentioned date.   SQL> select last_day ( sysdate ) from dual;   LAST_DAY --------- 30-NOV-10 By:-Gourav Kottawar 106

107. SQL> select last_day ('06-nov-2010') from dual;  LAST_DAY --------- 30-NOV-10 3. NEXT_DAY : to display next day of the mentioned date,that is if today if 6 th dec and today id Monday then when will next day  SQL> Select next_day ('06-dec-2010','Monday') from dual; By:-Gourav Kottawar 107

108. 4. MONTHS_BETWEEN : Calculates the months between the two dates.   select months_between ('02-feb-2010','03-Dec-2009') from dual;   MONTHS_BETWEEN('02-FEB-2010','03-DEC-2009') ------------------------------------------- 1.9677419  5. ADD_MONTHS adds month to the current date.   SQL> select add_months (sysdate,4) from dual;   ADD_MONTH --------- 23-MAR-11 By:-Gourav Kottawar 108

109. SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum By:-Gourav Kottawar 109

110. SQL COUNT(*) Example SELECT COUNT(*) FROM Orders; By:-Gourav Kottawar 110

111. MANIPULATION in DATES in SQL  1. TOCHAR: To retrieve date in a format other than default format.   select to_char ( sysdate,'DD -MM-YY') from dual; TO_CHAR( -------- 23-11-10   2.TO_DATE   select to_date ('06/07/2010','DD/MON/YY') from dual;  TO_DATE(' --------- 06/JUL/10 By:-Gourav Kottawar 111

112. The MAX() Function The MAX() function returns the largest value of the selected column. SQL MAX() Syntax:SELECT MAX(column_name) FROM table_name SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders By:-Gourav Kottawar 112

113. SQL MIN() Function   The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax:SELECT MIN(column_name) FROM table_name SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders By:-Gourav Kottawar 113

114. The SUM() Function The SUM() function returns the total sum of a numeric column. SQL SUM() Syntax: SELECT SUM(column_name) FROM table_name SELECT SUM(OrderPrice) AS OrderTotal FROM Orders By:-Gourav Kottawar 114

115. SQL STRING FUNCTION 1.SUBSTR Table Geography region_name store_name East Boston East New York West Los Angeles West San Diego Example 1: SELECT SUBSTR( store_name , 3) FROM Geography WHERE store_name = 'Los Angeles'; By:-Gourav Kottawar 115

116. Result : 's Angeles' Example 2: SELECT SUBSTR(store_name,2,4) FROM Geography WHERE store_name = 'San Diego'; Result : 'an D' By:-Gourav Kottawar 116

117. 2.LTRIM( str ) : Removes all white spaces from the beginning of the string. 3.RTRIM( str ) : Removes all white spaces at the end of the string. Example 1: SELECT TRIM('   Sample   ') from tablename ; Result : 'Sample' By:-Gourav Kottawar 117

118. Example 2: SELECT LTRIM('   Sample   '); Result : 'Sample   ' Example 3: SELECT RTRIM('   Sample   '); Result : '   Sample' By:-Gourav Kottawar 118

119. 4. Length(str) : Find the length of the string str . By:-Gourav Kottawar region_name store_name East Boston East New York West Los Angeles West San Diego 119

120. Example 1: SELECT Length(store_name) FROM Geography WHERE store_name = 'Los Angeles'; Result : 11 By:-Gourav Kottawar 120

121. Example 2: SELECT region_name, Length(region_name) FROM Geography; Result : By:-Gourav Kottawar region_name Length( region_name ) East 4 East 4 West 4 West 4 121

122. 5.Replace(str1, str2, str3) : In str1, find where str2 occurs, and replace it with str3. SELECT REPLACE(region_name, 'ast', 'astern') FROM Geography; By:-Gourav Kottawar region_name Eastern Eastern West West 122

123. 6. CONCAT CONCAT(str1, str2, str3, ...) : Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'. By:-Gourav Kottawar 123

124. SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston'; Result : 'EastBoston' SELECT region_name || ' ' || store_name FROM Geography WHERE store_name = 'Boston'; Result : 'East Boston' By:-Gourav Kottawar 124