Practical Questions for Data Base Management System
Lab 1
Connect with Oracle Data Base 11g
SQL> Connect
SQL> user_name: System
SQL> password: 1234
Connect With MYSQL Server
Username : System
DCL queries
Demo Database
Below is a selection from the Customers table used in the examples:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
SELECT * from table name;
SELECT column1, column2, ...
FROM table_name;
SELECT CustomerName, City FROM Customers;
SELECT DISTINCT column1, column2, ...
FROM table_name;
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
INSERT INTO Customers (CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway');
INSERT INTO Customers (CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien
15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1
0AA', 'UK');
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE FROM table_name WHERE condition;
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
The most commonly used SQL aggregate functions are:
MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT()).
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
SQL Commands are mainly categorized into five categories:
1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language
Create database or its
CREATE TABLE table_name
objects (table, index,
CREATE (column1 data_type, column2
function, views, store data_type, ...);
procedure, and triggers)
Delete objects from the
DROP DROP TABLE table_name;
database
Alter the structure of the ALTER TABLE table_name ADD
ALTER COLUMN column_name data_type;
database
Remove all records from a
table, including all spaces
TRUNCATE TRUNCATE TABLE table_name;
allocated for the records
are removed
Add comments to the data COMMENT 'comment_text' ON TABLE
COMMENT table_name;
dictionary
Rename an object existing RENAME TABLE old_table_name TO
RENAME new_table_name;
in the database
List of DML commands
Here are all the main DML (Data Manipulation Language) commands along
with their syntax:
Command Description Syntax
Insert data into a INSERT INTO table_name (column1, column2,
INSERT ...) VALUES (value1, value2, ...);
table
Update existing UPDATE table_name SET column1 = value1,
UPDATE column2 = value2 WHERE condition;
data within a table
Delete records
DELETE from a database DELETE FROM table_name WHERE condition;
table
Table control
LOCK LOCK TABLE table_name IN lock_mode;
concurrency
Call a PL/SQL or
CALL CALL procedure_name(arguments);
JAVA subprogram
Describe the
EXPLAIN EXPLAIN PLAN FOR SELECT * FROM
access path to table_name;
PLAN
data
List of DCL commands:
Two important DCL commands and their syntax are:
Command Description Syntax
Assigns new privileges to
a user account, allowing GRANT privilege_type
[(column_list)] ON
GRANT access to specific [object_type] object_name TO
database objects, actions, user [WITH GRANT OPTION];
or functions.
Removes previously
granted privileges from a REVOKE [GRANT OPTION FOR]
user account, taking away privilege_type [(column_list)]
REVOKE ON [object_type] object_name
their access to certain
FROM user [CASCADE];
database objects or
actions.
List of TCL Commands
Some TCL commands and their syntax are:
Command Description Syntax
BEGIN BEGIN TRANSACTION
Starts a new transaction [transaction_name];
TRANSACTION
Saves all changes made
COMMIT COMMIT;
during the transaction
Undoes all changes made
ROLLBACK ROLLBACK;
during the transaction
SQL
Command Example
SELECT SELECT * FROM employees;
INSERT INTO employees (first_name, last_name, email) VALUES
INSERT ('John', 'Doe', '[Link]@[Link]');
UPDATE employees SET email = '[Link]@[Link]' WHERE
UPDATE first_name = 'Jane' AND last_name = 'Doe';
DELETE DELETE FROM employees WHERE employee_id = 123;
CREATE CREATE TABLE employees ( employee_id INT PRIMARY KEY,
TABLE first_name VARCHAR(50), last_name VARCHAR(50));
ALTER
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
TABLE
DROP
DROP TABLE employees;
TABLE
WHERE SELECT * FROM employees WHERE department = 'Sales';
ORDER BY SELECT * FROM employees ORDER BY hire_date DESC;
SELECT e.first_name, e.last_name, d.department_name FROM
employees e JOIN departments d ON e.department_id =
JOIN d.department_id;
Conclusion
SQL commands are the foundation of an effective database management
system. Whether you are manipulating data, or managing data, SQL
provides all sets of tools. Now, with this detailed guide, we hope you have
gained a deep understanding of SQL commands, their categories, and
syntax with examples.
CREATE VIEWS in SQL
We can create a view using CREATE VIEW statement. A View can be
created from a single table or multiple tables.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
For storing a date or a date and time
value in a database,MySQL offers
the following data types:
DATE format YYYY-MM-DD
DATETIME format: YYYY-MM-DD HH:MI: SS
TIMESTAMP format: YYYY-MM-DD HH:MI: SS
YEAR format YYYY or YY
Now, come to some popular
functions in SQL date functions.
NOW()
Returns the current date and time.
Query:
SELECT NOW();
Output:
CURDATE()
Returns the current date.
Query:
SELECT CURDATE();
Output:
Difference Between SQL JOIN and Subquery
The main difference between SQL JOIN and subquery are mentioned in the
table below:
Subquery JOIN
A subquery is a query nested inside
another query and is used to return data A JOIN is a means for combining
that will be used in the main query as a fields from two tables by using
condition to further restrict the data to be values common to each.
retrieved.
Subqueries can be slower than JOINs, JOINs are generally faster than
especially if the subquery returns a large subqueries, especially for large
number of rows. datasets.
Subqueries can be more complex and JOINs can be easier to read and
harder to read, especially when there are understand, especially for simple
multiple levels of nesting. queries
PL/SQL TRIGGERS
PL/SQL triggers are special procedures that automatically executes (or “trigger”) in
response to certain events, such as data changes.
PL/SQL Triggers
PL/SQL Statement-level Triggers
PL/SQL Row-level Triggers
PL/SQL INSTEAD OF Triggers
PL/SQL Disable Triggers
PL/SQL Enable Triggers
PL/SQL Drop Triggers
PL/SQL Fixing Mutating Table Error