· 8 years ago · Aug 11, 2018, 09:50 PM
1SQL COMMANDS TYPE
21. DDL (Data Definition Language)
3 Data Definition Language, DDL, is the part of SQL that allows a database user to create and restructure database objects,
4 such as the creation or the deletion of a table.
5 CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX, CREATE VIEW, DROP VIEW
62. DML (Data Manipulation Language)
7 Data Manipulation Language, DML, is the part of SQL used to manipulate data within objects of a relational database.
8 INSERT, UPDATE, DELETE
93. DQL (Data Query Language)
10 SELECT
114. DCL (Data Control Language)
12 Data control commands in SQL allow you to control access to data within the database.
13 ALTER PASSWORD, GRANT, REVOKE, CREATE SYNONYM
145. Data administration commands
156. Transactional control commands
16 COMMIT Saves database transactions
17 ROLLBACK Undoes database transactions
18
19_______________________________________________________________________
20
21DDL-
221. CREATE DATABASE
23 mysql> CREATE DATABASE testDB;
242. DROP DATABASE
25 mysql> DROP DATABASE databasename;
263. CREATE TABLE
27 mysql> mysql> CREATE TABLE BOOKINGS(
28 hotel_no int(11) UNIQUE AUTO_INCREMENT,
29 room_no int(11) NOT NULL,
30 guest_no int(11),
31 date_from DATE DEFAULT GETDATE(),
32 date_to DATE,
33 PRIMARY KEY(hotel_no,guest_no,date_from),
34 FOREIGN KEY(hotel_no) REFERENCES HOTEL(hotel_no),
35 FOREIGN KEY(guest_no) REFERENCES GUESTS(guest_no)
36 );
374. DROP TABLE
38 mysql> DROP TABLE Shippers;
395. ALTER TABLE
40 a. ADD COLUMN
41 mysql> ALTER TABLE table_name
42 ADD col_name datatype;
43 b. DROP COLUMN
44 mysql> ALTER TABLE table_name
45 DROP COLUMN col_name;
46 c. MODIFY COLUMN
47 mysql> ALTER TABLE table_name
48 MODIFY COLUMN column_name datatype;
49
506. CREATE INDEX
51 Indexes are used to retrieve data from the database very fast.
52 The users cannot see the indexes, they are just used to speed up searches/queries.
53 mysql> CREATE INDEX index_name
54 ON table_name (column1, column2);
55
567. CREATE VIEW
57 In SQL, a view is a virtual table based on the result-set of an SQL statement.
58 A view contains rows and columns, just like a real table.
59 The fields in a view are fields from one or more real tables in the database.
60 You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
61
62 mysql> CREATE VIEW view_name AS
63 SELECT column1, column2
64 FROM table_name
65 WHERE condition;
66
67_______________________________________________________________________________
68
69DQL/DML -
701. SELECT
71 mysql> SELECT col_1,col_2 FROM table_name;
72 mysql> SELECT * FROM table_name;
73
742. WHERE with AND,OR,NOT,LIKE,BETWEEN,<=,>=,<>,IN
75 mysql> SELECT * FROM HOTEL
76 WHERE city='London' AND no=1234;
77
783. ORDER BY
79 mysql> SELECT * FROM HOTEL WHERE city='London'
80 ORDER BY col_1 ASC|DESC;
81
824. INSERT
83 mysql> INSERT INTO table_name (column1, column2, column3)
84 VALUES (value1, value2, value3);
85 mysql> INSERT INTO table_name
86 VALUES (value1, value2, value3);
87
885. NULL VALUES
89 A field with a NULL value is a field with no value. Not possible to test with comparision operators
90 mysql> SELECT column_names FROM table_name
91 WHERE col_1 IS NULL;
92 mysql> SELECT column_names FROM table_name
93 WHERE col_1 IS NOT NULL;
94
956. UPDATE
96 mysql> UPDATE table_name
97 SET col_1=val1, col_2=val2
98 WHERE condition;
99
1007. DELETE
101 mysql> DELETE FROM table_name
102 WHERE condition;
103
1048. LIMIT - different for mysql server,mysql,oracle
105 mysql> SELECT * FROM table LIMIT 5;
106
1079. MIN and MAX
108 mysql> SELECT MIN(col) FROM table_name;
109 mysql> SELECT MAX(column_name) FROM table_name;
110
11110. COUNT,AVG,SUM
112 mysql> SELECT COUNT(col) FROM table_name
113 mysql> SELECT AVG(col) FROM table_name
114 mysql> SELECT SUM(col) FROM table_name
115
11611. LIKE
117 %- represent zero or more chars
118 _- represent 1 char
119 mysql> SELECT * FROM table_name
120 WHERE col_1 LIKE pattern;
121
12212. IN - it is used to specify multiple values in where clause
123 mysql> SELECT * FROM table_name
124 WHERE col_1 IN (value1, value2);
125 mysql> SELECT * FROM table_name
126 WHERE col_1 IN (SELECT col from table_2);
127
12813. JOINS
129 a. (INNER) JOIN -
130 Returns records that have matching values in both tables
131 mysql> SELECT t1.col_1,t2.col_2 FROM t1
132 INNER JOIN t2 ON t1.col_1= t2.col_2;
133 b. LEFT (OUTER) JOIN:
134 Return all records from the left table, and the matched records from the right table
135 mysql> SELECT t1.col_1,t2.col_2 FROM t1
136 LEFT JOIN t2 ON t1.col_1= t2.col_2;
137 c. RIGHT (OUTER) JOIN:
138 Return all records from the right table, and the matched records from the left table
139 mysql> SELECT t1.col_1,t2.col_2 FROM t1
140 RIGHT JOIN t2 ON t1.col_1= t2.col_2;
141 d. FULL (OUTER) JOIN:
142 Return all records when there is a match in either left or right table
143 mysql> SELECT t1.col_1,t2.col_2 FROM t1
144 FULL JOIN t2 ON t1.col_1= t2.col_2;
145 e. Cross JOIN:
146 Return Carteisan Product of both tables
147 mysql> SELECT * FROM table1
148 CROSS JOIN table2;
14914. GROUP BY
150 mysql> SELECT COUNT(CustomerID), Country
151 FROM Customers
152 GROUP BY Country
153 ORDER BY COUNT(CustomerID) DESC;
154
15515. HAVING
156 The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
157 mysql> SELECT COUNT(CustomerID), Country
158 FROM Customers
159 GROUP BY Country
160 HAVING COUNT(CustomerID) > 5
161 ORDER BY COUNT(CustomerID) DESC;
162
16316. EXISTS
164 The EXISTS operator is used to test for the existence of any record in a subquery.
165 The EXISTS operator returns true if the subquery returns one or more records.
166 mysql> SELECT column_name(s)
167 FROM table_name
168 WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
169
17017. ANY,ALL
171
17218. COMMENTS
173 comments begin with --
174
175
176_____________________________________________________________________________________________
177
178Normalized form
179why?
180If data is unorganized in database there can be different problems or anomaly can happen in database.
181
182Normalization?
183Normalization is a process of organizing the data in database to avoid :
184a. Data Redundancy
185b. Insertion anomaly
186c. Update anomaly
187d. Deletion anomaly
188
1891. INVOICE ( c_no, c_name, c_addr, ISBN, title, auth_name, auth_country, qty, price )
190Types?
191a. 1NF
192 A relation is in 1NF if it has no repeating groups.
193
194 Here 1 has repeating groups.
195
196 1. INVOICE ( c_no(p), ISBN )
197 2. CUSTOMER ( c_no(p), c_name, c_addr )
198 3. CUSTOMER_BOOK ( c_no(p), ISBN(p), title, auth_name, auth_country, qty, price )
199
200 Repearting groups lead to inconsistent data.
201
202b. 2NF
203 2NF = 1NF + no partial dependency ( one particular set of non-key fields or a non-key field depend on only one of the key)
204
205 Here 3 has partial dependency. qty depend on both rest depend on only ISBN
206
207 3. CUSTOMER_BOOK ( c_no(p), ISBN(p), qty )
208 4. BOOK ( ISBN(p), title, auth_name, auth_country, price )
209
210c. 3NF
211 3NF = 2NF + no transitive dependency (relation among non key fields)
212
213 Here 4 has transitive dependency. auth_name,auth_country depend on each other
214
215 4. BOOK ( ISBN(p), title, auth_name, price )
216 5. AUTHOR (auth_name(p), auth_country)