INDEX

1 Steps to start Wamp Server - 3

2 Types of SQL commands - 8
3 Keys in SQL - 13
4 Integrity constraints in SQL - 14
5 Create Database - 19
6 Open Database - 20
7 Create Tables - 21
8 View Table Description using Desc - 24
9 View Table Description using Describe - 25
10 Insert Records -27
11 Primary Key -30
12 Foreign Key -33
13 Alter Table & Check constraint - 35
14 Rename table projects to April_Projects - 37
15 Show Table Structure -38
16 Update Table -39
17 Delete statement - 41
18 View All Records -42
19 View Selective Records using where clause - 43
20 View Selective Fields -44
21 Select Distinct - 45

22 AND operator -46

23 OR operator -47
24 Order By Clause - 48
25 LIKE operator -49
26 In Operator - 54
27 Between Operator - 55
28 View List of Databases - 56
29 View list of tables in a database - 57
30 Drop Command - 59
31 SUM function - 61
32 Group By statement - 62
33 Having clause - 63
34 Create Table Orders - 64
35 AVG Function - 66
36 Count Function - 67
37 Count Distinct - 68
38 Max Function - 69
39 Min Function - 70
40 UCASE Function - 71
41 LCASE Function - 72
42 MID Function - 73
43 LEN Function - 74
44 Cross Join - 75
45 Inner Join - 76
46 Left Outer Join - 77
47 Right Outer Join - 78
48 Union All - 81
49 COMMIT - 82

2
1. Steps to start Wamp Server

4
5

6
2 Types of SQL commands

8
9

10
 11

3 Keys in SQL

12
4 Integrity constraints in SQL

13

14
15

16
 17

5. Create Database

18
 6. Open Database

19

7. CREATE TABLE
[Link]

20
2. DEPT

21

3. PROJECT

22
8. View Table Description using Desc

[Link]

23

2. DEPT

24
 3. PROJECT

25

Insert Records
i) Persons- 19 records
Using default salary- 1 record

26
2. DEPT

27

3. PROJECT

28
11 Primary Key
i) Persons- P_id

29

ii) Dept- D_id

30
 iii) Project- Pj_id

31

12 Foreign Key
i) D_id in Persons references D_id in Department

32
 ii) Pj_id in Persons references Pj_id in Department

33

3 Alter Table & Check constraint

Add gender in Persons after Fname. Gender should be Male, Female or Others.

34
 Delete column Pj_duration from Project table.

35

14 Rename table projects to April_Projects

36
15 Show Table Structure
Persons

37

16 Update Table
Add details for gender column

38
Update P_id to 103 for person whose last name is "kucchal"

39

17 Delete statement
Delete the record of the person whose P_id is 8 .

40
 18 View All Records
Persons

41

19 View Selective Records using where

clause
Display the details of persons whose department id is 1111.

42
20 View Selective Fields
Display P_id, Fname & Salary of all persons

43

21 Select Distinct
List all the distinct city names from persons.

44
 22 AND operator
Display the details of persons whose reside is Delhi &
whose department id is 1111.

45

23 OR operator
Display the details of persons whose reside is Delhi or whose
department id is 1111

46
 24 Order By Clause
Display the details of all the persons in ascending order of their names

47

Display the details of all the persons in descending order of their Department

48
 25 LIKE operator
Display the details of all the persons whose city name contains the pattern "elh".

49

Display the details of all the persons whose second alphabet of the name is 'a'.

50
 Display the details of all the persons whose last name starts
with 'B' or 'S'.

51

Display the details of all the persons whose lastname does not start with 'B','S' or 'P'

52
 26 In Operator
Display the details of all the persons who reside in Delhi, Mumbai or Kolkata.

53

27 Between Operator
Display the details of all the persons whose department id is between 1112 to 1118

54
 28 View List of Databases

55

29 View list of tables in a database

56
Delete a record

57

30 Drop Command
Delete a database

58
 Delete table Project

59

31 SUM function
Calculate the total salaries of all the persons whose department id is 1111.

60
 32 Group By statement
Display D_id and corresponding total salaries of all the departments

61

33 Having clause
Display D_id and corresponding total salaries of all the departments
having total salaries greater than 70,000.

62
 34 Create Table
Orders(O_Id,OrderDate,OrderPrice,Customer)

63

INSERT RECORDS INTO

ORDERS

64
 35 AVG Function
Display the names of customers whose Order Price is greater than the
average Order Price from Orders table.

65

36 Count Function
Display the total number of records in orders table

66
 37 Count Distinct
Count Distinct Customers from Orders Table

67

38 Max Function
Display the maximum Order Price from Orders table.

68
 39 Min Function
Display the minimum Order Price from Orders table.

69

40 UCASE Function
Display the customer names from Orders table in capital letters or uppercase.

70
 41 LCASE Function
Display the customer names from Orders table in small letters or lowercase.

71

42 MID Function
Display the first 3 alphabets of city from Persons table.

72
 43 LEN Function
Display all the addresses along with their corresponding length from Persons table.

73

44 Cross Join
Tables- Persons & Department

74
 45 Inner Join
Tables- Persons & Department

75

46 Left Outer Join

Tables- Persons & Department

76
47 Right Outer Join
Tables- Persons & Department

77

Create table Project1

78
 Project2

79

48 Union All

80
49 COMMIT

81