SQL Commands: DDL and DML

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

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?

  • `DROP` (correct)
  • `ALTER`
  • `TRUNCATE`
  • `DELETE`

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?

<p><code>UPDATE</code> (A)</p> Signup and view all the answers

Which SQL command is used to remove rows from a table based on a specified condition?

<p><code>DELETE</code> (B)</p> Signup and view all the answers

Which of the following SQL integrity constraints ensures that all values in a column are different?

<p><code>UNIQUE</code> (C)</p> Signup and view all the answers

Which SQL constraint prevents actions that would break links between tables?

<p><code>FOREIGN KEY</code> (C)</p> Signup and view all the answers

Which SQL constraint is a combination of NOT NULL and UNIQUE?

<p><code>PRIMARY KEY</code> (A)</p> Signup and view all the answers

What is the purpose of the DEFAULT constraint in SQL?

<p>Sets a default value for a column if no value is specified. (A)</p> Signup and view all the answers

Which SQL constraint ensures that the values in a column satisfy a specific condition?

<p><code>CHECK</code> (D)</p> Signup and view all the answers

Which TCL command is used to permanently save changes made during a transaction?

<p><code>COMMIT</code> (C)</p> Signup and view all the answers

What is the purpose of the ROLLBACK command in SQL?

<p>To undo changes made during the current transaction. (D)</p> Signup and view all the answers

Which TCL command is used to create a point within a transaction to which you can later rollback?

<p><code>SAVEPOINT</code> (C)</p> Signup and view all the answers

What is the purpose of the UNION operator in SQL?

<p>To combine the results of two or more <code>SELECT</code> statements into a single result set, removing duplicate rows. (D)</p> Signup and view all the answers

How does UNION ALL differ from UNION in SQL?

<p><code>UNION ALL</code> includes all rows from the <code>SELECT</code> statements, including duplicates. (A)</p> Signup and view all the answers

What does the INTERSECT operator do in SQL?

<p>Returns only the rows that are common to both tables. (B)</p> Signup and view all the answers

Which SQL aggregate function calculates the average value of a numeric column?

<p><code>AVG()</code> (D)</p> Signup and view all the answers

Which SQL aggregate function returns the smallest value in a specified column?

<p><code>MIN()</code> (A)</p> Signup and view all the answers

Which aggregate function counts the number of rows in a table, including rows with NULL values?

<p><code>COUNT(*)</code> (A)</p> Signup and view all the answers

How do you rename a column in SQL?

<p>Using the <code>ALTER TABLE ... RENAME COLUMN ...</code> statement. (D)</p> Signup and view all the answers

What does the AS keyword do in a SQL SELECT statement?

<p>It assigns an alias to a column or table. (C)</p> Signup and view all the answers

Which SQL clause is used to sort the result-set of a query?

<p><code>ORDER BY</code> (A)</p> Signup and view all the answers

What is the purpose of the DISTINCT keyword in a SELECT statement?

<p>To select only unique values from a column. (C)</p> Signup and view all the answers

Which SQL operator is used to perform pattern matching?

<p><code>LIKE</code> (D)</p> Signup and view all the answers

What does the wildcard character % represent in a LIKE operator?

<p>Any sequence of zero or more characters. (C)</p> Signup and view all the answers

Which SQL clause is used to specify a range of values?

<p><code>BETWEEN</code> (B)</p> Signup and view all the answers

What is the purpose of the GROUP BY clause in SQL?

<p>To group rows that have the same values in specified columns into summary rows. (B)</p> Signup and view all the answers

Which SQL clause is used to filter the results of a GROUP BY clause?

<p><code>HAVING</code> (A)</p> Signup and view all the answers

What is a 'view', in the context of SQL databases?

<p>A saved query that can be used as a virtual table. (A)</p> Signup and view all the answers

What must be true in order to update data in a view?

<p>The view does not use any joins or aggregations. (D)</p> Signup and view all the answers

Which SQL join returns all rows from the left table and the matched rows from the right table?

<p><code>LEFT OUTER JOIN</code> (C)</p> Signup and view all the answers

Which of the following best describes the purpose of PL/SQL?

<p>An extension of SQL that combines procedural programming with database interaction. (A)</p> Signup and view all the answers

Which code block is the first in a PL/SQL procedure?

<p><code>DECLARE</code> (B)</p> Signup and view all the answers

What is the purpose of the DBMS_OUTPUT.PUT_LINE function in PL/SQL?

<p>To display output to the user. (A)</p> Signup and view all the answers

In PL/SQL, what keyword is used to handle exceptions?

<p><code>EXCEPTION</code> (D)</p> Signup and view all the answers

Flashcards

What is DDL?

Statements used to define the database structure.

What is DML?

Statements used to manipulate data within a database.

What is DCL?

Statements used to control access to database objects.

What is TCL?

Statements used to manage transactions within a database.

Signup and view all the flashcards

What is DQL?

Statements used to query data from a database.

Signup and view all the flashcards

What is CREATE TABLE?

Creates a new table in the database.

Signup and view all the flashcards

What is DROP TABLE?

Removes a table from the database.

Signup and view all the flashcards

What is ALTER TABLE?

Modifies the structure of an existing table.

Signup and view all the flashcards

What is TRUNCATE TABLE?

Removes all rows from a table, but keeps the table structure.

Signup and view all the flashcards

What is INSERT INTO?

Adds new rows to a table.

Signup and view all the flashcards

What is UPDATE?

Changes existing rows in a table.

Signup and view all the flashcards

What is DELETE FROM?

Removes rows from a table based on a condition.

Signup and view all the flashcards

What is SELECT?

Retrieves specific columns from a table.

Signup and view all the flashcards

What is COMMIT?

Saves all changes made during the current transaction.

Signup and view all the flashcards

What is ROLLBACK?

Undoes all changes made during the current transaction.

Signup and view all the flashcards

What is SAVEPOINT?

Creates a point within a transaction to which you can rollback.

Signup and view all the flashcards

SQL Constraints

SQL constraints specify rules for the data

Signup and view all the flashcards

What is NOT NULL?

Ensures column values can't be NULL.

Signup and view all the flashcards

What is UNIQUE constraint?

Ensures that all values in a column are different.

Signup and view all the flashcards

What is PRIMARY KEY

NOT NULL and UNIQUE combined.

Signup and view all the flashcards

What is FOREIGN KEY?

Prevents actions that would destroy links between tables.

Signup and view all the flashcards

What is CHECK constraint?

Ensures column values satisfies a specific condition.

Signup and view all the flashcards

What is DEFAULT constraint?

Sets column default values.

Signup and view all the flashcards

What is JOIN?

Combines rows from tables, based on a related column.

Signup and view all the flashcards

What is NATURAL JOIN?

Joins tables based on same name, data types, uses implicit condition.

Signup and view all the flashcards

What is INNER JOIN?

Returns records of table with matching values based on explicit condition.

Signup and view all the flashcards

What is LEFT OUTER JOIN?

Returns all records from the left table & matched records from the right.

Signup and view all the flashcards

What is RIGHT OUTER JOIN?

Returns from right table, plus matches from the left table

Signup and view all the flashcards

What is FULL OUTER JOIN?

It returns all the records when there is a match in either left or right table

Signup and view all the flashcards

What is a VIEW?

A virtual table based on a query result.

Signup and view all the flashcards

What is a Function?

Functions are useful to create a modular design.

Signup and view all the flashcards

What is a procedure?

Procedures support modular design

Signup and view all the flashcards

What is Cursor?

A temporary memory area allocated to the database server

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 table Stud and default value Noida
  • 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 employees
  • SELECT COUNT(empid) FROM employees; // ignore null value counts employee ids from employee, null values will be ignored
  • SELECT 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 country
  • SELECT * 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 A
  • SELECT first_name FROM employees WHERE first_name LIKE 'Adam%' This grabs first names from employees that stat with Adam
  • ELECT first_name FROM employees WHERE first_name LIKE '%ann' (ends with) This grabs first names from employees that end with ann
  • SELECT 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 Staff
  • SELECT 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 customers
  • SELECT 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 UK
  • SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK'); Selects all customers that are not located in Germany, France, or UK
  • SELECT * 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, and continue

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.

Quiz Team

Related Documents

More Like This

Use Quizgecko on...
Browser
Browser