Podcast
Questions and Answers
Which SQL command type is primarily used for retrieving data from a database?
Which SQL command type is primarily used for retrieving data from a database?
- Data Manipulation Language (DML)
- Data Query Language (DQL) (correct)
- Data Control Language (DCL)
- Data Definition Language (DDL)
Which of the following DDL commands is used to remove a table from a database?
Which of the following DDL commands is used to remove a table from a database?
- `DROP` (correct)
- `ALTER`
- `TRUNCATE`
- `DELETE`
Which SQL statement is used to add a new column to an existing table?
Which SQL statement is used to add a new column to an existing table?
- `ALTER TABLE ADD COLUMN` (correct)
- `INSERT COLUMN`
- `UPDATE TABLE ADD COLUMN`
- `ADD COLUMN`
Which DML command is used to modify existing data within a table?
Which DML command is used to modify existing data within a table?
Which SQL command is used to remove rows from a table based on a specified condition?
Which SQL command is used to remove rows from a table based on a specified condition?
Which of the following SQL integrity constraints ensures that all values in a column are different?
Which of the following SQL integrity constraints ensures that all values in a column are different?
Which SQL constraint prevents actions that would break links between tables?
Which SQL constraint prevents actions that would break links between tables?
Which SQL constraint is a combination of NOT NULL
and UNIQUE
?
Which SQL constraint is a combination of NOT NULL
and UNIQUE
?
What is the purpose of the DEFAULT
constraint in SQL?
What is the purpose of the DEFAULT
constraint in SQL?
Which SQL constraint ensures that the values in a column satisfy a specific condition?
Which SQL constraint ensures that the values in a column satisfy a specific condition?
Which TCL command is used to permanently save changes made during a transaction?
Which TCL command is used to permanently save changes made during a transaction?
What is the purpose of the ROLLBACK
command in SQL?
What is the purpose of the ROLLBACK
command in SQL?
Which TCL command is used to create a point within a transaction to which you can later rollback?
Which TCL command is used to create a point within a transaction to which you can later rollback?
What is the purpose of the UNION
operator in SQL?
What is the purpose of the UNION
operator in SQL?
How does UNION ALL
differ from UNION
in SQL?
How does UNION ALL
differ from UNION
in SQL?
What does the INTERSECT
operator do in SQL?
What does the INTERSECT
operator do in SQL?
Which SQL aggregate function calculates the average value of a numeric column?
Which SQL aggregate function calculates the average value of a numeric column?
Which SQL aggregate function returns the smallest value in a specified column?
Which SQL aggregate function returns the smallest value in a specified column?
Which aggregate function counts the number of rows in a table, including rows with NULL
values?
Which aggregate function counts the number of rows in a table, including rows with NULL
values?
How do you rename a column in SQL?
How do you rename a column in SQL?
What does the AS
keyword do in a SQL SELECT
statement?
What does the AS
keyword do in a SQL SELECT
statement?
Which SQL clause is used to sort the result-set of a query?
Which SQL clause is used to sort the result-set of a query?
What is the purpose of the DISTINCT
keyword in a SELECT
statement?
What is the purpose of the DISTINCT
keyword in a SELECT
statement?
Which SQL operator is used to perform pattern matching?
Which SQL operator is used to perform pattern matching?
What does the wildcard character %
represent in a LIKE
operator?
What does the wildcard character %
represent in a LIKE
operator?
Which SQL clause is used to specify a range of values?
Which SQL clause is used to specify a range of values?
What is the purpose of the GROUP BY
clause in SQL?
What is the purpose of the GROUP BY
clause in SQL?
Which SQL clause is used to filter the results of a GROUP BY
clause?
Which SQL clause is used to filter the results of a GROUP BY
clause?
What is a 'view', in the context of SQL databases?
What is a 'view', in the context of SQL databases?
What must be true in order to update data in a view?
What must be true in order to update data in a view?
Which SQL join returns all rows from the left table and the matched rows from the right table?
Which SQL join returns all rows from the left table and the matched rows from the right table?
Which of the following best describes the purpose of PL/SQL?
Which of the following best describes the purpose of PL/SQL?
Which code block is the first in a PL/SQL procedure?
Which code block is the first in a PL/SQL procedure?
What is the purpose of the DBMS_OUTPUT.PUT_LINE
function in PL/SQL?
What is the purpose of the DBMS_OUTPUT.PUT_LINE
function in PL/SQL?
In PL/SQL, what keyword is used to handle exceptions?
In PL/SQL, what keyword is used to handle exceptions?
Flashcards
What is DDL?
What is DDL?
Statements used to define the database structure.
What is DML?
What is DML?
Statements used to manipulate data within a database.
What is DCL?
What is DCL?
Statements used to control access to database objects.
What is TCL?
What is TCL?
Signup and view all the flashcards
What is DQL?
What is DQL?
Signup and view all the flashcards
What is CREATE TABLE?
What is CREATE TABLE?
Signup and view all the flashcards
What is DROP TABLE?
What is DROP TABLE?
Signup and view all the flashcards
What is ALTER TABLE?
What is ALTER TABLE?
Signup and view all the flashcards
What is TRUNCATE TABLE?
What is TRUNCATE TABLE?
Signup and view all the flashcards
What is INSERT INTO?
What is INSERT INTO?
Signup and view all the flashcards
What is UPDATE?
What is UPDATE?
Signup and view all the flashcards
What is DELETE FROM?
What is DELETE FROM?
Signup and view all the flashcards
What is SELECT?
What is SELECT?
Signup and view all the flashcards
What is COMMIT?
What is COMMIT?
Signup and view all the flashcards
What is ROLLBACK?
What is ROLLBACK?
Signup and view all the flashcards
What is SAVEPOINT?
What is SAVEPOINT?
Signup and view all the flashcards
SQL Constraints
SQL Constraints
Signup and view all the flashcards
What is NOT NULL?
What is NOT NULL?
Signup and view all the flashcards
What is UNIQUE constraint?
What is UNIQUE constraint?
Signup and view all the flashcards
What is PRIMARY KEY
What is PRIMARY KEY
Signup and view all the flashcards
What is FOREIGN KEY?
What is FOREIGN KEY?
Signup and view all the flashcards
What is CHECK constraint?
What is CHECK constraint?
Signup and view all the flashcards
What is DEFAULT constraint?
What is DEFAULT constraint?
Signup and view all the flashcards
What is JOIN?
What is JOIN?
Signup and view all the flashcards
What is NATURAL JOIN?
What is NATURAL JOIN?
Signup and view all the flashcards
What is INNER JOIN?
What is INNER JOIN?
Signup and view all the flashcards
What is LEFT OUTER JOIN?
What is LEFT OUTER JOIN?
Signup and view all the flashcards
What is RIGHT OUTER JOIN?
What is RIGHT OUTER JOIN?
Signup and view all the flashcards
What is FULL OUTER JOIN?
What is FULL OUTER JOIN?
Signup and view all the flashcards
What is a VIEW?
What is a VIEW?
Signup and view all the flashcards
What is a Function?
What is a Function?
Signup and view all the flashcards
What is a procedure?
What is a procedure?
Signup and view all the flashcards
What is Cursor?
What is Cursor?
Signup and view all the flashcards
Study Notes
- There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL
DDL Commands
- CREATE is used to create tables and has the syntax:
CREATE TABLE table_name(Col_name1 datatype(), Col_name2 datatype(),... Col_namen datatype(), );
- An example of CREATE is:
CREATE TABLE DDL ( id int, DDL_Type varchar(50), DDL_Value int);
- ALTER TABLE table_name ADD to add a column with the syntax
ALTER TABLE table_name ADD Col_name datatype()...;
- Example:
ALTER TABLE DDL ADD COLUMN DDL_Example varchar(50);
- ALTER TABLE table_name can DROP COLUMN column_name
- Example:
ALTER TABLE DDL DROP COLUMN DDL_Example
- DROP TABLE table_name deletes a table
- TRUNCATE TABLE table_name deletes the data inside a table, name table keeps
- RENAME table table_name to new_table_name and renames tables
- Example:
RENAME table DDL to DD;
DML Commands
- INSERT INTO Table_Name VALUES(); and inserts a row
- To insert into table DDL, use the command:
INSERT INTO DDL (id, DDL_Type, DDL_Value) VALUES (2, 'DML', 123);
- UPDATE tablename set to(calculation); is used to update column
- Example syntax:
UPDATE dd1 SET DDL_Value = 555 WHERE DDL_Type = 'DML';
- DELETE FROM table_name
- Example: DELETE FROM DDL;
DQL Commands
- SELECT column1, column2, ... FROM table_name WHERE condition; to grab certain columns
- To select all columns you can use
SELECT * FROM Customers WHERE Country='Mexico';
- It is possible to select all columns where a condition is true with:
SELECT * FROM Customers WHERE CustomerID > 80;
SQL Integrity Constraints
- SQL constraints specify rules for data in a table and limit the type of data that can go into the table
- NOT NULL ensures a column cannot have a NULL value
- UNIQUE ensures all values in a column are different
- PRIMARY KEY is a combination of NOT NULL and UNIQUE constraints.
- FOREIGN KEY prevents actions that would destroy links between tables
- CHECK ensures the values in a column satisfies a specific condition
- DEFAULT sets a default value for a column if no value is specified
NOT NULL example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int );
UNIQUE example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID));
PRIMARY KEY example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID));
FOREIGN KEY example
- Foreign key creates a referencing relation to another table with CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons (PersonID));
CHECK example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18) );
DEFAULT example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes');
CREATE TABLE Stud (ID int NOT NULL, Name varchar(255), Age int, Location varchar(255) DEFAULT 'Noida');
is an example with tableStud
and default valueNoida
- Inserting the VALUES (5, 'Hema', 27,DEFAULT); will set Location to Noida
Transaction Control Language
- TCL commands include: COMMIT, ROLLBACK, and SAVEPOINT
- UPDATE STUDENT SET NAME = 'Sherlock' WHERE STUDENT_NAME = ‘Jolly';
- Rollback allows for going back to a previous state
- SAVEPOINT allows the user to specify certain points
Set Operations
- Set operations are UNION, UNION ALL, INTERSECTION, MINUS
SQL Aggregate Functions
- Common aggregate functions in SQL include: MIN(), MAX(), SUM(), AVG(), and COUNT()
- An aggregate function in SQL returns one value after calculating multiple values of a column
SELECT AVG (salary) FROM employees;
returns the average from the salary column in table employeesSELECT COUNT(empid) FROM employees; // ignore null value
counts employee ids from employee, null values will be ignoredSELECT COUNT(*) FROM employees; // count total no of rows
counts total number of rows in employees
Rename
- Rename a table:
ALTER TABLE table_name RENAME TO new_table_name;
- Example:
ALTER TABLE std RENAME TO students;
- Rename an attribute:
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
- Example:
ALTER TABLE student RENAME COLUMN sname TO name;
AS
- Renames CustomerID to ID
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
- Grabs OrderID, OrderDate, and CustomerName from customer and order tables.
SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
SQL Keywords
- DISTINCT is used to return only distinct (different) values
SELECT DISCTINCT (NAME) FROM Customers;
returns the distinct names in Customers- ORDER BY sorts the result-set in ascending or descending order
SELECT * FROM Customers ORDER BY Country;
orders the customers table by the countrySELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
sorts CustomerName in descending order
LIKE (% and _)
- This filter SQL records on specific string matches
SELECT first_name FROM employees WHERE first_name LIKE 'A%' (start with)
This grabs first names fromm employess that start with ASELECT first_name FROM employees WHERE first_name LIKE 'Adam%'
This grabs first names from employees that stat with AdamELECT first_name FROM employees WHERE first_name LIKE '%ann' (ends with)
This grabs first names from employees that end with annSELECT first_name FROM employees WHERE first_name LIKE ' _ _ '
selects first_name from employees WHERE first_name LIKE ' _ _ 'SELECT title FROM titles WHERE title NOT LIKE 'Staff'
selects name that is not StaffSELECT first_name, last_name FROM employees WHERE first_name LIKE 'Z%' AND last_name LIKE 'Z%'
selects names that START with Z
More LIKE Example
SELECT * FROM Customers WHERE country LIKE 'U_';
selects country name that starts with U and is followed by one character'SELECT * FROM Customers WHERE last_name LIKE 'R%t' OR last_name LIKE '%e';
selects last names that start with R, end with t, or end with e
BETWEEN
- This selects values within a given range
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
selects all products with price between 10 and 20
GROUP BY
- This groups rows that have the same values into summary rows
- This is often used with aggregate functions
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
groups CustomerID by country
HAVING
- WHERE keyword cannot be used with aggregate functions
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
lists the number of customers in each country, but only if the country has more than 5 customersSELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
lists the number of customers, only in more than 5 in descending order
IN
- This specifies multiple values in the WHERE clause
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
Selects all customers that are located in Germany, France, or UKSELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
Selects all customers that are not located in Germany, France, or UKSELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
selects Country from the Country column in suppliers
EXISTS
- This checks whether the result of a correlated nested query is empty (contains no tuples) or not
- The result of EXISTS is a boolean value True or False
SELECT fname, Iname FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Customers.customer_id = Orders.customer_id);
will return something if exists
JOIN
- This combines rows from two or more tables, based on a related column between them
- Natural Join joins two tables based on same attribute name and datatypes
- The resulting table contains all the attributes of both the table but keep only one copy of each common column
- Inner Join returns records that have matching values in both tables based on explicit condition
- Left Outer Join returns all records from the left table, and the matched records from the right table
- Right Outer Join returns all records from the right table, and the matched records from the left table
- Full Outer Join returns all records when there is a match in either left or right table
Natural Join Example
- There is natural join on Student NATURAL JOIN Marks, which combines based on shared attributes
View
- Views in SQL are kind of virtual tables that store structure information
- A View can either have all the rows of a table or specific rows based on certain condition for security
- It can simplify a complex query
CREATE VIEW Brazil_Customers AS SELECT CustomerName, ContactName FROM Customers WHERE Country = 'Brazil';
is an example- You can select from a view with with:
Select * from Brazil_Customers;
- Use Multiple table with:
CREATE VIEW MarksView AS SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS FROM StudentDetails, StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;
CREATE OR REPLACE VIEW Brazil_Customers AS SELECT CustomerName, ContactName, City FROM Customers WHERE Country = "Brazil";
- You can drop a view with: Delete view Brazil_Customers;
Create or replace rules
- Rules for CREATE OR REPLACE statements include not changing certain things
- Rules to avoid including are: --Cannot change column name -- Cannot change column Data Type --Cannot change order of columns
select * from expensive_products;
select * from tb_product_info;
alter table tb_product_info add column prod_config varchar(100)
View Update Rules
- View queries should be formed using just 1 table/view
SQL/PSM
-
PL/SQL: Procedural Language extensions to SQL in block structured
-
DECLARE declaration statements; variable_name data_type := value // assignment
-
BEGIN executable statements Variable:= &z; //input dbms_output.put_line(var); //output
-
EXCEPTIONS exception handling statements
-
END;
-
Example
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
- taking input for variable a
a integer := &a ;
-- taking input for variable b
b integer := &b ;
c integer;
BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);
END;
/
Control Flow
- IF condition THEN statements END IF;
- IF condition-1 THEN statements; ELSIF condition-2 THEN statement; ELSIF condition-3 THEN statment; ELSE statements; END IF;
- Example DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; /;
Loops
FOR index IN [REVERSE ] lower_bound..upper_bound LOOP statements END LOOP;
- A basic loop example
BEGIN
FOR I IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
Loop exmaples
- Example
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
- A FOR loop in reverse
DECLARE
a number(2);
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
- A nested for loop
DECLARE
i number(1);
j number(1);
BEGIN
FOR I IN 1..3 LOOP
FOR J IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and jis: ' || j);
END LOOP;
END LOOP;
END;
/
- A while loop example
DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
- Here is an EXIT example for loop
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
Loop breaking
- There are also
break
, andcontinue
Functions
- A function example structure shows how to show function name, parameter, the datatypes used and return types
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END;
- An adder function with parameters n1 and n2 to add together
create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/
Example
DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/
- Another example using select count to count customers
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
- Example
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
Procedures
- An example CREATE PROCEDURE statement CREATE [OR REPLACE] PROCEDURE name [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END;
CREATE PROCEDURE P_UPDATE_SALARY
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + (SALARY 0.10)
WHERE DEPARTMENT_ID=10;
DBMS_OUTPUT.PUT_LINE('SALARY UPDATED');
BEGIN
P_UPDATE_SALARY();
END;
/
CREATE PROCEDURE P_UPDATE_SALARY(P_DEPT_ID IN HUMBER, P_SAL_INCR NUMBER)
IS
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + (SALARY P_SAL_INCR)
WHERE DEPARTMENT_ID=P_DEPT_ID;
DBMS_OUTPUT.PUT_LINE('SALARY UPDATED FOR '11P_DEPT_ID);
END;
DECLARE
a number(5):=&a;
- -
get ID from user
b number(5):=&b; --get Increment value from user
BEGIN
P_UPDATE_SALARY(a, b);
END;
/
Cursor
- A Cursor is a temporary memory allocated by the database server performing data manipulation language
- It holds the multiple rows returned by the SQL statement
- implicit cursor - ecplicit cursor
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.