Q. How do you select all records from the table?
Select * from table_name;
Q. What is a join?
Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one “joined” collection of data.
Q. How do you add record to a table?
A. INSERT into table_name VALUES (‘ALEX’, 33 , ‘M’);
Q. How do you add a column to a table?
ALTER TABLE Department ADD (AGE, NUMBER);
Q. How do you change value of the field?
A. UPDATE EMP_table set number = 200 where item_munber = ‘CD’;
update name_table set status = 'enable' where phone = '4161112222';
update SERVICE_table set REQUEST_DATE = to_date ('2006-03-04 09:29', 'yyyy-mm-dd hh24:MM') where phone = '4161112222';
Q. What does COMMIT do?
A. Saving all changes made by DML statements
Q. What is a primary key?
A. The column (columns) that has completely unique data throughout the table is known as the primary key field.
Q. What are foreign keys?
A. Foreign key field is a field that links one table to another table’s primary or foreign key.
Q. What is the main role of a primary key in a table?
A. The main role of a primary key in a data table is to maintain the internal integrity of a data table.
Q. Can a table have more than one foreign key defined?
A table can have any number of foreign keys defined. It can have only one primary key defined.
Q. List all the possible values that can be stored in a BOOLEAN data field.
There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false).
Q. What is the highest value that can be stored in a BYTE data field?
A. The highest value that can be stored in a BYTE field is 255. or from -128 to 127. Byte is a set of Bits that represent a single character. Usually there are 8 Bits in a Byte, sometimes more, depending on how the measurement is being made. Each Char requires one byte of memory and can have a value from 0 to 255 (or 0 to 11111111 in binary).
Q. Describe how NULLs work in SQL?
The NULL is how SQL handles missing values. Arithmetic operation with NULL in SQL will return a NULL.
Q. What is Normalization?
A. The process of table design is called normalization.
Q. What is Trigger?
A. Trigger will execute a block of procedural code against the database when a table event occurs. A2. A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, in this case the trigger has been activated.
Q. Can one select a random collection of rows from a table?
Yes. Using SAMPLE clause. Example:
SELECT * FROM EMPLOYEES SAMPLE(10);
10% of rows selected randomly will be returned.
Q. You issue the following query:
SELECT FirstName FROM StaffListWHERE FirstName LIKE '_A%‘
Which names would be returned by this query? Choose all that apply.
Allen
CLARK
JACKSON
David
Q. Write a SQL SELECT query that only returns each city only once from Students table? Do you need to order this list with an ORDER BY clause?
A. SELECT DISTINCT City FROM Students;
Q. What is DML and DDL?
DML and DDL are subsets of SQL. DML stands for Data Manipulation Language and DDL – Data Definition Language.
DML consist of INSERT, UPDATE and DELETE
DDL commands
CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX.
CREATE/ALTER/DROP VIEW
Q. Write SQL SELECT query that returns the first and last name of each instructor, the Salary, and gives each of them a number.
A. SELECT FirstName, LastName, Salary, ROWNUM FROM Instructors;
Q. Is the WHERE clause must appear always before the GROUP BY clause in SQL SELECT ?
A. Yes. The proper order for SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.
Q. Which of the following statements are Data Manipulation Language commands?
INSERT
UPDATE
GRANT
TRUNCATE
CREATE
Ans. A and B. The INSERT and UPDATE statements are Data Manipulation Language (DML) commands. GRANT is a Data Control Language (DCL) command. TRUNCATE and CREATE are Data Definition Language (DDL) commands
Question: Describe SQL comments.
A. SQL comments are introduced by two consecutive hyphens (--) and ended by the end of the line.
Q. Difference between TRUNCATE, DELETE and DROP commands?
A. The DELETE command is used to remove 'some or all rows from a table.
TRUNCATE removes ALL rows from a table. The operation cannot be rolled back
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.
Select * from table_name;
Q. What is a join?
Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one “joined” collection of data.
Q. How do you add record to a table?
A. INSERT into table_name VALUES (‘ALEX’, 33 , ‘M’);
Q. How do you add a column to a table?
ALTER TABLE Department ADD (AGE, NUMBER);
Q. How do you change value of the field?
A. UPDATE EMP_table set number = 200 where item_munber = ‘CD’;
update name_table set status = 'enable' where phone = '4161112222';
update SERVICE_table set REQUEST_DATE = to_date ('2006-03-04 09:29', 'yyyy-mm-dd hh24:MM') where phone = '4161112222';
Q. What does COMMIT do?
A. Saving all changes made by DML statements
Q. What is a primary key?
A. The column (columns) that has completely unique data throughout the table is known as the primary key field.
Q. What are foreign keys?
A. Foreign key field is a field that links one table to another table’s primary or foreign key.
Q. What is the main role of a primary key in a table?
A. The main role of a primary key in a data table is to maintain the internal integrity of a data table.
Q. Can a table have more than one foreign key defined?
A table can have any number of foreign keys defined. It can have only one primary key defined.
Q. List all the possible values that can be stored in a BOOLEAN data field.
There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false).
Q. What is the highest value that can be stored in a BYTE data field?
A. The highest value that can be stored in a BYTE field is 255. or from -128 to 127. Byte is a set of Bits that represent a single character. Usually there are 8 Bits in a Byte, sometimes more, depending on how the measurement is being made. Each Char requires one byte of memory and can have a value from 0 to 255 (or 0 to 11111111 in binary).
Q. Describe how NULLs work in SQL?
The NULL is how SQL handles missing values. Arithmetic operation with NULL in SQL will return a NULL.
Q. What is Normalization?
A. The process of table design is called normalization.
Q. What is Trigger?
A. Trigger will execute a block of procedural code against the database when a table event occurs. A2. A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, in this case the trigger has been activated.
Q. Can one select a random collection of rows from a table?
Yes. Using SAMPLE clause. Example:
SELECT * FROM EMPLOYEES SAMPLE(10);
10% of rows selected randomly will be returned.
Q. You issue the following query:
SELECT FirstName FROM StaffListWHERE FirstName LIKE '_A%‘
Which names would be returned by this query? Choose all that apply.
Allen
CLARK
JACKSON
David
Q. Write a SQL SELECT query that only returns each city only once from Students table? Do you need to order this list with an ORDER BY clause?
A. SELECT DISTINCT City FROM Students;
Q. What is DML and DDL?
DML and DDL are subsets of SQL. DML stands for Data Manipulation Language and DDL – Data Definition Language.
DML consist of INSERT, UPDATE and DELETE
DDL commands
CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX.
CREATE/ALTER/DROP VIEW
Q. Write SQL SELECT query that returns the first and last name of each instructor, the Salary, and gives each of them a number.
A. SELECT FirstName, LastName, Salary, ROWNUM FROM Instructors;
Q. Is the WHERE clause must appear always before the GROUP BY clause in SQL SELECT ?
A. Yes. The proper order for SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.
Q. Which of the following statements are Data Manipulation Language commands?
INSERT
UPDATE
GRANT
TRUNCATE
CREATE
Ans. A and B. The INSERT and UPDATE statements are Data Manipulation Language (DML) commands. GRANT is a Data Control Language (DCL) command. TRUNCATE and CREATE are Data Definition Language (DDL) commands
Question: Describe SQL comments.
A. SQL comments are introduced by two consecutive hyphens (--) and ended by the end of the line.
Q. Difference between TRUNCATE, DELETE and DROP commands?
A. The DELETE command is used to remove 'some or all rows from a table.
TRUNCATE removes ALL rows from a table. The operation cannot be rolled back
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.
