sql excercise1(latest) - file · web viewselect sid, fname, lname from student order...
TRANSCRIPT
![Page 1: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/1.jpg)
Table of Contents:
Connect to Oracle Database........................................................................................................................2
Finding your user name...............................................................................................................................3
Create STUDENT table.................................................................................................................................4
SELECT on a Table........................................................................................................................................6
To Select all columns...............................................................................................................................6
To select specific columns.......................................................................................................................6
To select columns in any specific order...................................................................................................6
“Order By” to sort data............................................................................................................................7
“Order By” on NULL data.........................................................................................................................8
“Order By” w/ DESC.................................................................................................................................8
“Order BY” on multiple columns..............................................................................................................9
“Order By” using Number........................................................................................................................9
Major (vs) Minor sort.............................................................................................................................10
Alias a column........................................................................................................................................11
Comments: Single Line, Multi Line.........................................................................................................12
WHERE Clause...........................................................................................................................................14
Basic Where clauses..............................................................................................................................14
OR clause...............................................................................................................................................14
Double (vs) Single Quote.......................................................................................................................14
Operations on NULL value.....................................................................................................................15
Order of precedence.............................................................................................................................16
IN operator............................................................................................................................................16
NULL + NOT IN.......................................................................................................................................17
BETWEEN/ NOT BETWEEN.....................................................................................................................18
LIKE........................................................................................................................................................18
DISTINCT (vs) Group BY.........................................................................................................................19
FETCH Clause.........................................................................................................................................19
![Page 2: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/2.jpg)
Connect to Oracle Database
![Page 3: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/3.jpg)
Finding your user name
![Page 4: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/4.jpg)
Create STUDENT table
DROP TABLE STUDENT;
CREATE TABLE student (sid NUMBER, fname CHAR(20), lname CHAR(20), class_code CHAR(2), grade_pt NUMBER(3,2) );
describe student
INSERT INTO student VALUES (501, 'Ravi', 'Nandigam', 'CS', 4.00);INSERT INTO student VALUES (502, 'Mike', 'B', 'EC', 3.92);INSERT INTO student VALUES (503, 'Tammy', 'A', 'ME', 2.10);INSERT INTO student VALUES (504, 'Elmer', 'Andy', 'CS', 5.00);INSERT INTO student VALUES (505, 'Srini', 'M', 'EC', 1.92);INSERT INTO student VALUES (506, 'Amar', 'Kaka', 'CI', 0.92);INSERT INTO student VALUES (507, 'Zaheer', 'Khan', NULL, NULL);INSERT INTO student VALUES (508, 'Mahesh', 'ch', 'ME', NULL);INSERT INTO student VALUES (509, 'Susan', 'abc', 'EC', 2.00);INSERT INTO student VALUES (510, 'Sarada', 'K', 'CS', 4.50);COMMIT;
![Page 5: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/5.jpg)
![Page 6: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/6.jpg)
SELECT on a Table
To Select all columnsSELECT * FROM STUDENT;
To select specific columnsselect fname, class_code, grade_pt from student;
To select columns in any specific orderselect class_code, lname, sid from student;
![Page 7: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/7.jpg)
“Order By” to sort dataselect * from student order by fname;
![Page 8: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/8.jpg)
“Order By” on NULL dataselect * from student order by grade_pt;
“Order By” w/ DESCselect * from student order by grade_pt desc;
![Page 9: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/9.jpg)
“Order BY” on multiple columnsselect sid, fname, lname from student order by class_code, grade_pt;
“Order By” using Numberselect sid, fname, lname from student order by sid;select sid, fname, lname from student order by 1;
![Page 10: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/10.jpg)
Major (vs) Minor sortselect * from student order by lname, class_code;
![Page 11: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/11.jpg)
Alias a columnselect sid “Student ID”, fname “First Name”, lname “Last Name” from student;
SELECT sid as “Student Id” from student;
![Page 12: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/12.jpg)
Comments: Single Line, Multi Line
![Page 13: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/13.jpg)
![Page 14: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/14.jpg)
WHERE Clause
Basic Where clauses
OR clause
Double (vs) Single Quote
![Page 15: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/15.jpg)
Operations on NULL value
Note: NULL means UNKNOWN data, so EQUAL(=) won’t work.
![Page 16: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/16.jpg)
Order of precedence
Note: () , NOT, AND, OR
IN operator
![Page 17: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/17.jpg)
NULL + NOT IN
![Page 18: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/18.jpg)
BETWEEN/ NOT BETWEEN
LIKE
![Page 19: SQL Excercise1(Latest) - file · Web viewselect sid, fname, lname from student order by class_code, grade_pt;](https://reader036.vdocuments.us/reader036/viewer/2022062906/5a6fafb37f8b9aa2538b53eb/html5/thumbnails/19.jpg)
Note: CHAR data will be padded with spaces at the end. Hence, second query above didn’t return any record.
Note2: Use trim to remove trailing spaces
DISTINCT (vs) Group BY
FETCH Clause