DBMS Module 4 and some sample SQL queries for each topic

Data Definition Commands:

• CREATE TABLE: This command is used to create a new table in a database.

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255) NOT NULL, ContactName VARCHAR(255), Country VARCHAR(255) ); 

• ALTER TABLE: This command is used to modify an existing table in a database.

ALTER TABLE Customers ADD Email VARCHAR(255); 

• DROP TABLE: This command is used to delete an existing table in a database.

DROP TABLE Customers; 

Integrity constraints:

• PRIMARY KEY: This constraint is used to uniquely identify each record in a table.

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT NOT NULL, OrderDate DATE, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); 

• FOREIGN KEY: This constraint is used to link two tables together.

CREATE TABLE OrderDetails ( OrderDetailID INT PRIMARY KEY, OrderID INT NOT NULL, ProductID INT NOT NULL, Quantity INT, FOREIGN KEY (OrderID) REFERENCES Orders(OrderID), FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ); 

• CHECK: This constraint is used to limit the values that can be inserted into a column.

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(255), LastName VARCHAR(255), Salary DECIMAL(10,2), CONSTRAINT SalaryCheck CHECK (Salary > 0) ); 

Data Manipulation commands:

• INSERT: This command is used to insert new records into a table.

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country) VALUES (1, 'Alfreds Futterkiste', 'Maria Anders', 'Germany'); 

• UPDATE: This command is used to update existing records in a table.

UPDATE Customers SET ContactName = 'Ana Trujillo' WHERE CustomerID = 1; 

• DELETE: This command is used to delete existing records from a table.

DELETE FROM Customers WHERE CustomerID = 1; 

Set and string operations:

• UNION: This operation is used to combine the results of two or more SELECT statements into a single result set.

SELECT City FROM Customers UNION SELECT City FROM Suppliers; 

• LIKE: This operator is used to match patterns in a string.

SELECT * FROM Customers WHERE ContactName LIKE '%Maria%'; 

Aggregate function-group by, having:

• COUNT: This function is used to count the number of rows in a table or a group.

SELECT COUNT(*) FROM Customers; 

• GROUP BY: This clause is used to group the rows in a result set by one or more columns.

SELECT Country, COUNT(*) FROM Customers GROUP BY Country; 

• HAVING: This clause is used to filter the results of a GROUP BY query based on a condition.

SELECT Country, COUNT(*) FROM Customers GROUP BY Country HAVING COUNT(*) > 5; 

Views in SQL:

• CREATE VIEW: This command is used to create a virtual table based on the result of a SELECT statement.

CREATE VIEW CustomerOrders AS SELECT Customers.CustomerName, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 

• SELECT FROM VIEW: This command is used to query the virtual table created by a VIEW.

SELECT * FROM CustomerOrders; 

Joins:

• INNER JOIN: This type of join is used to return only the rows that have matching values in both tables.

SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; 

• LEFT JOIN: This type of join is used to return all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, NULL values are returned.

SELECT Customers.CustomerName, Orders.OrderDate FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 

• RIGHT JOIN: This type of join is used to return all the rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, NULL values are returned.

SELECT Customers.CustomerName, Orders.OrderDate FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 

Nested and complex queries:

• Subqueries: A subquery is a SELECT statement nested inside another SELECT statement, and it is used to retrieve data that will be used in the main query.

SELECT CustomerName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders); 

• EXISTS: This operator is used to check whether a subquery returns any rows.

SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID); 

Triggers:

• CREATE TRIGGER: This command is used to create a trigger that will automatically execute when a specific event occurs.

CREATE TRIGGER UpdateOrderTotal AFTER INSERT ON OrderDetails FOR EACH ROW UPDATE Orders SET TotalAmount = TotalAmount + NEW.Quantity * (SELECT Price FROM Products WHERE ProductID = NEW.ProductID) WHERE OrderID = NEW.OrderID; 

I hope this helps you prepare for your exam! Remember to practice writing SQL queries and understand the concepts behind them to ensure your success on the exam. Good luck!