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';