MIS Final
Create Table SQL
CREATE TABLE department ( DeptID text(15), Name text(20), PRIMARY KEY (DeptID));
Primary and Foreign Key SQL
CREATE TABLE section ( SecID text(20), DeptID text(20), CourseNum integer, SecLetter text(5), Semester text(20), Room text(20), TimeCode text(20), ProfID integer, PRIMARY KEY (SecID), FOREIGN KEY (DeptID, CourseNum) REFERENCES course, FOREIGN KEY (ProfID) REFERENCES instructor, FOREIGN KEY (TimeCode) REFERENCES timeslot);
Load Data SQL
INSERT INTO instructor VALUES (10211, 'John', 'Smith', 'CoRE 110', '732- 666-5555', '[email protected]', 'ENG');
Composition of Multiple Criteria SQL
SELECT * FROM Customer WHERE state = 'NJ' AND city = 'Hamilton';
Alter Table Attributes SQL
ALTER TABLE instructor ADD Salary Currency; ALTER TABLE instructor DROP Salary Currency;
Cartesian Join SQL
SELECT CUSTOMERS.CustomerID, FirstName, LastName, ORDERS.CustomerID, OrderID, OrderDate FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.CustomerID = ORDERS.CustomerID;
Inner Join SQL
SELECT CUSTOMERS.CustomerID, FirstName, LastName, OrderID, OrderDate FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CustomerID = ORDERS.CustomerID;
Inequality Predicates SQL
SELECT FirstName, LastName, Credits FROM Student WHERE Credits*800 > 40000 ORDER BY LastName ASC, FirstName ASC;
Specify criteria's that each row in the result must satisfy SQL
SELECT FirstName, LastName, Major FROM Student WHERE Major = 'Business';
The SELECT Clause: Calculated Fields SQL
SELECT LastName, FirstName, ID, Credits, Credits*800 FROM Students
The Rename Operation in the SELECT Clause SQL
SELECT LastName, FirstName, ID, Credits, Credits*800 as TotalTuition FROM Students
Sort all rows in the query result according to the order of the designated attribute SQL
SELECT LastName, FirstName, ID, GradePoints, Credits, GradePoints/Credits as GPA FROM Students ORDER BY GradePoints/Credits desc
Select Clause SQL
SELECT LastName, FirstName, ID, State, BirthDate, Major, GradePoints, Credits, FinancialAid FROM Students
Top Predicate SQL
SELECT TOP 5 FirstName, LastName, Credits FROM Student ORDER BY Credits DESC;
IN SQL
SELECT lastname, firstname, city, state FROM customer WHERE state IN ('NY', 'PA', 'CT');
Between SQL
SELECT orderid, orderdate FROM orders WHERE orderdate BETWEEN #4/15/2013# AND #4/22/2013#;
What was SQL originally and who created it?
Sequel and IBM
What does SQL stand for?
Structured Query Language
Remove a Table SQL
To remove a relation from an SQL database, we use the drop table command. The command DROP TABLE r;
Update Data SQL
UPDATE instructor SET Salary = 65000 WHERE FirstName='John' and LastName='Smith';
Remove Data SQL
We can use the delete command to delete tuples from a relation. The command DELETE FROM instructor; would delete all tuples from the instructor relation. Other forms of the delete command allow specific tuples to be deleted according to WHERE conditions. For instance, DELETE FROM instructor WHERE LName = 'Smith';
