-
Notifications
You must be signed in to change notification settings - Fork 16
Expand file tree
/
Copy pathDS_SQL.sql
More file actions
168 lines (127 loc) · 3.31 KB
/
Copy pathDS_SQL.sql
File metadata and controls
168 lines (127 loc) · 3.31 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
-- introduction of Database
-- intro - Sql
-- Create Database
-- drop Database
-- select Database
-- create table
-- insert query
-- where
-- and operator
-- or operator
-- update query
-- delete
-- like
-- top
-- order by
-- group by
-- distinct keyword
-- Constraints
-- join
-- unions claus
-- truncate table
-- having
-- view
-- useful function
-- date : 18 sep 2025
-- list of database
show databases;
-- create a database
create database CWPC11;
-- select database
use CWPC11;
-- delete
-- drop database CWPC11;
-- Date : 30 Sep 2025
-- create a table
create table Employee(
id int,
name varchar(100),
salary int,
city varchar(100),
dept enum('sales','hr','other'),
dob date
);
-- show list tables
show tables;
-- show table record
select * from employee;
-- insert record into table
insert into employee (id,name,salary,city,dept,dob)
value(101,'Joy',25000,'Mumbai','sales','1990-12-01'),
(102,'toy',45000,'pune','other','1995-12-01'),
(103,'roy',65000,'surat','hr','1991-12-01'),
(104,'koy',75000,'goa','sales','1992-12-01'),
(105,'boy',95000,'bhuj','sales','1993-12-01');
-- 2nd way
create table Employee1(
id int primary key,
name varchar(100),
salary int,
city varchar(100),
dept enum('sales','hr','other'),
dob date
);
-- show table record
select * from employee1;
-- insert record into table
insert into employee1 (id,name,salary,city,dept,dob)
value(101,'Joy',25000,'Mumbai','sales','1990-12-01'),
(102,'toy',45000,'pune','other','1995-12-01'),
(103,'roy',65000,'surat','hr','1991-12-01'),
(104,'koy',75000,'goa','sales','1992-12-01'),
(105,'boy',95000,'bhuj','sales','1993-12-01');
-- 04 Oct 2025
-- list of database
show databases;
-- select database
use classicmodels;
-- list of tables
show tables;
-- select tables
select * from customers;
-- count()
select count(customernumber) from customers;
-- where
-- count only USA customers
select count(customernumber) from customers where country = "USA";
-- print all usa customers
select * from customers where country = "USA";
-- -- print all usa customers from state NY
select * from customers where country = "USA" and state = "NY";
-- Date : 25 oct 2025
-- Constraints (key)
-- primary key
-- Example
create database cwpc090;
use cwpc090;
create table students(
studentId int primary key,
Name varchar(50),
age int);
insert into students(studentId,name,age) values (101,"Rohan",55);
insert into students(studentId,name,age) values (102,"Joy",75);
select * from students;
-- Foreign key
create table course(
courseid int primary key,
coursename varchar(100)
);
create table enrollments(
enrollmentid int primary key,
studentID int,
courseid int,
foreign key(courseid) references course(courseid)
);
insert into course(courseid,coursename) values(1,"maths");
insert into course(courseid,coursename) values(2,"java");
insert into enrollments(enrollmentid,studentid,courseid) values (101,1005,1);
insert into enrollments(enrollmentid,studentid,courseid) values (102,1004,2);
-- unique key
create table students1(
studentId int primary key,
Name varchar(50),
age int,
email varchar(100) unique key);
insert into students1(studentId,name,age,email) values (101,"Rohan",55,"rohan@cwpc.in");
insert into students1(studentId,name,age,email) values (102,"Joy",75,"Joy@cwpc.in");
select * from students1;