edu Toidayhoc

Author: alien

  • Khóa học miễn phí SQL – Operators nhận dự án làm có lương

    SQL – Operators

    Table of content

    What is SQL Operator?

    An SQL operator is a reserved word or a character used primarily in an SQL statement”s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

    An SQL operator can be either a unary or binary operator. A unary operator (example unary + or unary – ) uses only one operand to perform the unary operation, whereas the binary operator (example + or – etc) uses two operands to perform the binary operation.

    Types of Operator in SQL

    supports following types of operators:

    • Operators used to negate conditions

    SQL Arithmetic Operators

    SQL Arithmetic Operators are used to perform mathematical operations on the numerical values. SQL provides following operators to perform mathematical operations.

    Here is a list of all the arithmetic operators available in SQL.

    Operator Description Example
    + Addition 10 + 20 = 30
    – Subtraction 20 – 30 = -10
    * Multiplication 10 * 20 = 200
    / Division 20 / 10 = 2
    % Modulus 5 % 2 = 1

    SQL Comparison Operators

    SQL Comparison Operators test whether two given expressions are the same or not. These operators are used in SQL conditional statements while comparing one expression with another and they return a Boolean value which can be either TRUE or FALSE. The result of an SQL comparison operation can be UNKNOWN when one or another operand has it”s value as NULL.

    Here is a list of all the comparison operators available in SQL.

    Operator Description Example
    = Equal to 5 = 5 returns TRUE
    != Not equal 5 != 6 returns TRUE
    <> Not equal 5 <> 4 returns TRUE
    > Greater than 4 > 5 returns FALSE
    < Less than 4 < 5 returns TRUE
    >= Greater than or equal to 4 >= 5 returns FALSE
    <= Less than or equal to 4 <= 5 returns TRUE
    !< Not less than 4 !< 5 returns FALSE
    !> Not greater than 4 !> 5 returns TRUE

    SQL Logical Operators

    SQL Logical Operators are very similar to comparison operators and they test for the truth of some given condition. These operators return a Boolean value which can be either a TRUE or FALSE. The result of an SQL logical operation can be UNKNOWN when one or another operand has it”s value as NULL.

    Here is a list of all the logical operators available in SQL.

    Operator Description Example
    ALL TRUE if all of a set of comparisons are TRUE. Example
    AND TRUE if all the conditions separated by AND are TRUE.
    ANY TRUE if any one of a set of comparisons are TRUE. Example
    BETWEEN TRUE if the operand lies within the range of comparisons.
    EXISTS TRUE if the subquery returns one or more records
    IN TRUE if the operand is equal to one of a list of expressions.
    LIKE TRUE if the operand matches a pattern specially with wildcard.
    NOT Reverses the value of any other Boolean operator.
    OR TRUE if any of the conditions separated by OR is TRUE
    IS NULL TRUE if the expression value is NULL.
    SOME TRUE if some of a set of comparisons are TRUE. Example
    UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates). Example

    SQL Operator Precedence

    The operator precedence in SQL is the sequence in which the SQL evaluates the different operators in a given expression. The operators with higher precedence get evaluated first.

    Following table lists all SQL operators as per their precedence. The operators with the highest precedence are at the top and the operators with the lowest precedence are at the bottom.

    Operator Operation
    +, – identity, negation
    *, / multiplication, division
    +, – addition, subtraction
    =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparison
    NOT logical negation
    AND conjunction
    OR inclusion

    Example

    Consider the following SQL statement:

    SELECT 20 - 3 * 5;

    This will result in the following. Here multiplication operator gets evaluated first and then subtraction happens.

    5

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Syntax nhận dự án làm có lương

    SQL – Syntax

    Table of content

    What is SQL Syntax?

    SQL syntax is a unique set of rules and guidelines to be followed while writing SQL statements. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax.

    All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

    Case Sensitivity

    The most important point to be noted here is that SQL is case insensitive, which means SELECT and Select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database.

    SQL Table

    Let us consider a table with the name CUSTOMERS shown below, and use it as a reference to demonstrate all the SQL Statements on the same.

    ID NAME AGE ADDRESS SALARY
    1 Ramesh 32 Ahmedabad 2000.00
    2 Khilan 25 Delhi 1500.00
    3 kaushik 23 Kota 2000.00
    4 Chaitali 25 Mumbai 6500.00
    5 Hardik 27 Bhopal 8500.00
    6 Komal 22 Hyderabad 4500.00
    7 Muffy 24 Indore 10000.00

    SQL Statements

    This tutorial lists down various SQL statements. Most of them are compatible with MySQL, Oracle, Postgres and SQL Server databases.

    All the SQL statements require a semicolon (;) at the end of each statement. Semicolon is the standard way to separate different SQL statements which allows to include multiple SQL statements in a single line.

    All the SQL Statements given in this tutorial have been tested with a MySQL server on Linux and Windows.

    SQL CREATE DATABASE Statement

    To store data within a database, you first need to create it. This is necessary to individualize the data belonging to an organization.

    You can create a database using the following syntax −

    CREATE DATABASE database_name;

    Let us try to create a sample database sampleDB in SQL using the CREATE DATABASE statement −

    CREATE DATABASE sampleDB

    SQL USE Statement

    Once the database is created, it needs to be used in order to start storing the data accordingly. Following is the syntax to change the current location to required database −

    USE database_name;

    We can set the previously created sampleDB as the default database by using the USE statement in SQL −

    USE sampleDB;

    SQL DROP DATABASE Statement

    If a database is no longer necessary, you can also delete it. To delete/drop a database, use the following syntax −

    DROP DATABASE database_name;

    You can also drop the sampleDB database by using the DROP DATABASE statement in SQL −

    DROP DATABASE sampleDB;

    SQL CREATE TABLE Statement

    In an SQL driven database, the data is stored in a structured manner, i.e. in the form of tables. To create a table, following syntax is used −

    CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

    The following code block is an example, which creates a CUSTOMERS table given above, with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

    CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

    SQL DESC Statement

    Every table in a database has a structure of its own. To display the structure of database tables, we use the DESC statements. Following is the syntax −

    DESC table_name;

    The DESC Statement, however, only works in few RDBMS systems; hence, let us see an example by using DESC statement in the MySQL server −

    DESC CUSTOMERS;

    SQL INSERT INTO Statement

    The SQL INSERT INTO Statement is used to insert data into database tables. Following is the syntax −

    INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

    The following example statements would create seven records in the empty CUSTOMERS table.

    INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500),
(3, ''kaushik'', 23, ''Kota'', 2000),
(4, ''Chaitali'', 25, ''Mumbai'', 6500),
(5, ''Hardik'', 27, ''Bhopal'', 8500),
(6, ''Komal'', 22, ''Hyderabad'', 4500),
(7, ''Muffy'', 24, ''Indore'', 10000);

    SQL SELECT Statement

    In order to retrieve the result-sets of the stored data from a database table, we use the SELECT statement. Following is the syntax −

    SELECT column1, column2....columnN FROM table_name;

    To retrieve the data from CUSTOMERS table, we use the SELECT statement as shown below.

    SELECT * FROM CUSTOMERS;

    SQL UPDATE Statement

    When the stored data in a database table is outdated and needs to be updated without having to delete the table, we use the UPDATE statement. Following is the syntax −

    UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

    To see an example, the following query will update the ADDRESS for a customer whose ID number is 6 in the table.

    UPDATE CUSTOMERS SET ADDRESS = ''Pune'' WHERE ID = 6;

    SQL DELETE Statement

    Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions. This is done using the DELETE FROM statement. Following is the syntax −

    DELETE FROM table_name WHERE  {CONDITION};

    The following code has a query, which will DELETE a customer, whose ID is 6.

    DELETE FROM CUSTOMERS WHERE ID = 6;

    SQL DROP TABLE Statement

    To delete a table entirely from a database when it is no longer needed, following syntax is used −

    DROP TABLE table_name;

    This query will drop the CUSTOMERS table from the database.

    DROP TABLE CUSTOMERS;

    SQL TRUNCATE TABLE Statement

    The TRUNCATE TABLE statement is implemented in SQL to delete the data of the table but not the table itself. When this SQL statement is used, the table stays in the database like an empty table. Following is the syntax −

    TRUNCATE TABLE table_name;

    Following query delete all the records of the CUSTOMERS table −

    TRUNCATE TABLE CUSTOMERS;

    SQL ALTER TABLE Statement

    The ALTER TABLE statement is used to alter the structure of a table. For instance, you can add, drop, and modify the data of a column using this statement. Following is the syntax −

    ALTER TABLE table_name
{ADD|DROP|MODIFY} column_name {data_type};

    Following is the example to ADD a New Column to the CUSTOMERS table using ALTER TABLE command −

    ALTER TABLE CUSTOMERS ADD SEX char(1);

    SQL ALTER TABLE Statement (Rename)

    The ALTER TABLE statement is also used to change the name of a table as well. Use the syntax below −

    ALTER TABLE table_name RENAME TO new_table_name;

    Following is the example to RENAME the CUSTOMERS table using ALTER TABLE command −

    ALTER TABLE CUSTOMERS RENAME TO NEW_CUSTOMERS;

    SQL DISTINCT Clause

    The DISTINCT clause in a database is used to identify the non-duplicate data from a column. Using the SELECT DISTINCT statement, you can retrieve distinct values from a column. Following is the syntax −

    SELECT DISTINCT column1, column2....columnN FROM   table_name;

    As an example, let us use the DISTINCT keyword with a SELECT query. The repetitive salary 2000.00 will only be retrieved once and the other record is ignored.

    SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

    SQL WHERE Clause

    The WHERE clause is used to filter rows from a table by applying a condition. Following is the syntax to retrieve filtered rows from a table −

    SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION;

    The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000, using the SELECT statement −

    SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;

    SQL AND/OR Operators

    The AND/OR Operators are used to apply multiple conditions in the WHERE clause. Following is the syntax −

    SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

    The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000 AND age is less than 25, using the SELECT statement −

    SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;

    SQL IN Clause

    The IN Operator is used to check whether the data is present in the column or not, using the WHERE clause. Following is the syntax −

    SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name IN (val-1, val-2,...val-N);

    For an example, we want to display records with NAME equal to ”Khilan”, ”Hardik” and ”Muffy” (string values) using IN operator as follows −

    SELECT * FROM CUSTOMERS
WHERE NAME IN (''Khilan'', ''Hardik'', ''Muffy'');

    SQL BETWEEN Clause

    The BETWEEN Operator is used to retrieve the values from a table that fall in a certain range, using the WHERE clause. Following is the syntax −

    SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name BETWEEN val-1 AND val-2;

    Let us try to the BETWEEN operator to retrieve CUSTOMERS records whose AGE is between 20 and 25.

    SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 20 AND 25;

    SQL LIKE Clause

    The LIKE Operator is used to retrieve the values from a table that match a certain pattern, using the WHERE clause. Following is the syntax −

    SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name LIKE { PATTERN };

    As an example, let us try to display all the records from the CUSTOMERS table, where the SALARY starts with 200.

    SELECT * FROM CUSTOMERS WHERE SALARY LIKE ''200%

    SQL ORDER BY Clause

    The ORDER BY Clause is used to arrange the column values in a given/specified order. Following is the syntax −

    SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION
ORDER BY column_name {ASC|DESC};

    In the following example we are trying to sort the result in an ascending order by the alphabetical order of customer names −

    SELECT * FROM CUSTOMERS ORDER BY NAME ASC;

    SQL GROUP BY Clause

    The GROUP BY Clause is used to group the values of a column together. Following is the syntax −

    SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name;

    We are trying to group the customers by their age and calculate the average salary for each age group using the following query −

    SELECT ADDRESS, AGE, SUM(SALARY)
AS TOTAL_SALARY FROM CUSTOMERS
GROUP BY ADDRESS, AGE;

    SQL COUNT Function

    The COUNT Function gives the number of non-null values present in the specified column. Following is the syntax −

    SELECT COUNT(column_name)
FROM   table_name
WHERE  CONDITION;

    Let us see an example −

    SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

    SQL HAVING Clause

    The HAVING clause is also used to filter a group of rows by applying a condition. Following is the syntax −

    SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

    In the following example, we are trying to retrieve all records from the CUSTOMERS table where the sum of their salary is greater than 5000 −

    SELECT ADDRESS, AGE, SUM(SALARY) AS
TOTAL_SALARY FROM CUSTOMERS GROUP BY
ADDRESS, AGE HAVING TOTAL_SALARY >=5000
ORDER BY TOTAL_SALARY DESC;

    SQL CREATE INDEX Statement

    To create an index on a database table, SQL provides the CREATE INDEX statement. Following is the syntax −

    CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

    Let us create an index for the column named ”NAME” in the existing CUSTOMERS table using the following query −

    CREATE INDEX sample_index on CUSTOMERS(NAME);

    SQL DROP INDEX Statement

    The DROP INDEX statement is used to drop an index from a table. Following is the syntax −

    DROP INDEX index_name ON table_name;

    Let us drop the index we created previously for the column named ”NAME” in the existing CUSTOMERS table using the following query −

    DROP INDEX sample_index on CUSTOMERS;

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Create Database nhận dự án làm có lương

    SQL – CREATE Database

    Table of content

    A database is a structured collection of data that is stored in a computer system. They are used to store and retrieve the data efficiently. Databases can be created using different query languages, and is one such language.

    CREATE Database Statement

    The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in SQL. If you are creating your database on Linux or Unix, then database names are case-sensitive, even though SQL keywords are case-insensitive. If you are working on Windows then this restriction does not apply.

    Syntax

    Following is the syntax to create a database in SQL −

    CREATE DATABASE DatabaseName;

    Here, the DatabaseName is the name of the database that we want to create. The database name can contain any valid identifiers, such as number, letters, or underscores. But a DatabaseName cannot be a keyword available in SQL.

    While creating a database, you may encounter an error such as ERROR 1044 (42000): Access denied for user ”krishna”@”localhost” to database ”DatabaseName”, this means that you do not have the necessary privileges to create a database. To create a database, you need to have admin previleges.

    Example

    Following is an example to create a database testDB using SQL CREATE DATABASE statement −

    CREATE DATABASE testDB;

    List Databases using SQL

    Once the database testDB is created, you can check it in the list of databases using SQL command SHOW DATABASES;.

    Syntax

    SHOW DATABASES;

    Output

    The output will be displayed as −

    Database
    master
    performance_schema
    information_schema
    mysql
    testDB

    Use/Select Databases using SQL

    We can now set the testDB as the default database by using the USE statement in SQL.

    Syntax

    USE testDB;

    That”s it! we have successfully created a database in SQL. Now, we can create tables and other database objects within this new database.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Databases nhận dự án làm có lương

    SQL Databases

    Table of content

    SQL Databases

    SQL or Relational databases are used to store and manage the data objects that are related to one another, i.e. the process of handling data in a relational database is done based on a relational model.

    This relational model is an approach to manage data in a structured way (using tables). A system used to manage these is known as Relational Database Management System (RDBMS).

    SQL Database Table Structure

    SQL database server stores data in table form. are database objects used to collect data in Row and Column format. Rows represent the entities whereas columns define the attributes of each entity in a table.

    Columns: Columns are vertical elements in a table. Each column in a table holds specific attribute information, and column properties such as column name and data types (Integer, Char, String, etc).

    Rows: Rows are horizontal elements in a table and users can add data or retrieve by executing SQL queries.

    Types of SQL Databases

    There are many popular RDBMS available to work with. Some of the most popular RDBMS are listed below −

    • MySQL
    • MS SQL Server
    • ORACLE
    • MS ACCESS
    • PostgreSQL
    • SQLite

    This SQL databases tutorial gives a brief overview of these RDBMS specified above. This would help you to compare their basic features.

    MySQL

    is an open source SQL database, which is developed by a Swedish company, MySQL AB. MySQL is pronounced as “my ess-que-ell,” in contrast with SQL, pronounced “sequel.”

    MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.

    MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server.

    History

    • 1994 – Development of MySQL by Michael Widenius & David Axmark beginning in 1994.

    • 1995 – First internal release on 23rd May 1995.

    • 1998 – Windows Version was released on the 8th January 1998 for Windows 95 and NT.

    • 2001 – Version 3.23 released beta from June 2000, and production release January 2001.

    • 2003 – Version 4.0 was released on August 2002 as beta, and as production release on March 2003 (unions).

    • 2004 – Version 4.1 was released on June 2004 as beta, and as production release on October 2004.

    • 2005 – Version 5.0 was released on March 2005 as beta, and as a production release on October 2005.

    • 2008 – Sun Microsystems acquired MySQL AB on the 26th February 2008, and Oracle Version 5.1 had its production release on 27th November 2008.

    • 2010 – Oracle acquired Sun Microsystems on 27th January 2010 and general availability of version 5.5 was on 3rd December 2010.

    • 2013 – General availability of Version 5.6 was enabled on 5th February 2013

    • 2015 – General availability of Version 5.7 was enabled on 21st October 2015

    • 2018 – General availability of Version 8.0 was enabled on 19th April 2018, and is the latest version of MySQL.

    Features

    • High Performance.
    • High Availability.
    • Scalability and Flexibility Run anything.
    • Robust Transactional Support.
    • Web and Data Warehouse Strengths.
    • Strong Data Protection.
    • Comprehensive Application Development.
    • Management Ease.
    • Open Source Freedom and 24 x 7 Support.
    • Lowest Total Cost of Ownership.

    MS SQL Server

    is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are −

    • T-SQL
    • ANSI SQL

    History

    • 1987 – Sybase releases SQL Server for UNIX.

    • 1988 – Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.

    • 1989 – Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.

    • 1990 – SQL Server 1.1 is released with support for Windows 3.0 clients.

    • 2000 – Microsoft releases SQL Server 2000.

    • 2001 – Microsoft releases XML for SQL Server Web Release 1 (download).

    • 2002 – Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).

    • 2002 – Microsoft releases SQLXML 3.0.

    • 2006 – Microsoft releases SQL Server 2005 on January 14, 2006.

    • 2008 – Microsoft releases SQL Server 2008 on November 6, 2008. And R2 version is released on July 20, 2010.

    • 2012 – Microsoft releases SQL Server 2012 on May 20, 2012.

    • 2014 – Microsoft releases SQL Server 2014 on June 5, 2014.

    • 2016 – Microsoft releases SQL Server 2016 on June 1, 2016.

    • 2017 – Microsoft releases SQL Server 2017 on September 29, 2017.

    • 2019 – Microsoft releases SQL Server 2019 on November 4, 2019.

    • 2022 – Microsoft releases SQL Server 2022 on November 16, 2022.

    Features

    • High Performance
    • High Availability
    • Database mirroring
    • Database snapshots
    • CLR integration
    • Service Broker
    • DDL triggers
    • Ranking functions
    • Row version-based isolation levels
    • XML integration
    • TRY…CATCH
    • Database Mail

    ORACLE

    is a very large multi-user based database management system. Oracle is a relational database management system developed by ”Oracle Corporation”.

    Oracle works to efficiently manage its resources, a database of information among the multiple clients requesting and sending data in the network.

    It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.

    History

    Oracle began in 1977 and celebrating its 46 wonderful years in the industry (from 1977 to 2023).

    • 1977 – Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work.

    • 1979 – Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).

    • 1981 – RSI started developing tools for Oracle.

    • 1982 – RSI was renamed to Oracle Corporation.

    • 1983 – Oracle released version 3.0, rewritten in C language and ran on multiple platforms.

    • 1984 – Oracle version 4.0 was released. It contained features like concurrency control – multi-version read consistency, etc.

    • 1985 – Oracle version 4.0 was released. It contained features like concurrency control – multi-version read consistency, etc.

    • 2007 – Oracle released Oracle11g. The new version focused on better partitioning, easy migration, etc.

    • 2013 – Oracle Database 12c R1 version was released in July, 2013; and R2 version was released on cloud in August 2016, and released on-prem in March 2017.

    • 2018 – Oracle Database 18c version was initially released in July 2018.

    • 2019 – Oracle Database 19c version was released in February 2019.

    • 2020 – Oracle Database 21c version was released in December 2020.

    • 2023 – Oracle Database 23c version was released in April 2023.

    Features

    • Concurrency
    • Read Consistency
    • Locking Mechanisms
    • Quiesce Database
    • Portability
    • Self-managing database
    • SQL*Plus
    • ASM
    • Scheduler
    • Resource Manager
    • Data Warehousing
    • Materialized views
    • Bitmap indexes
    • Table compression
    • Parallel Execution
    • Analytic SQL
    • Data mining
    • Partitioning

    MS ACCESS

    Microsoft Access is one of the most popular Microsoft products. It is an entry-level database management software. It is not only inexpensive but also a powerful database for small-scale projects.

    uses the Jet database engine, which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL). It comes with the professional edition of MS Office package. MS Access has easy to-use intuitive graphical interface.

    • 1992 – Access version 1.0 was released.

    • 1993 – Access 1.1 released to improve compatibility with inclusion the Access Basic programming language.

    • The most significant transition was from Access 97 to Access 2000.

    • 2007 – Access 2007, a new database format was introduced ACCDB which supports complex data types such as multi valued and attachment fields.

    • 2010 – Microsoft Access 2010 introduced a new version of the ACCDB format supported hosting Access Web services on a SharePoint 2010 server.

    • 2013 – Microsoft Access 2013 offers traditional Access desktop applications plus a significantly updated SharePoint 2013 web service

    • 2021 – Microsoft Access is no longer included in one-time purchase version of Microsoft Office 2021, but remains within the Microsoft 365 counterpart.

    Features

    • Users can create tables, queries, forms and reports and connect them together with macros.

    • Option of importing and exporting the data to many formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc.

    • There is also the Jet Database format (MDB or ACCDB in Access 2007), which can contain the application and data in one file. This makes it very convenient to distribute the entire application to another user, who can run it in disconnected environments.

    • Microsoft Access offers parameterized queries. These queries and Access tables can be referenced from other programs like VB6 and .NET through DAO or ADO.

    • The desktop editions of Microsoft SQL Server can be used with Access as an alternative to the Jet Database Engine.

    • Microsoft Access is a file server-based database. Unlike the client-server relational database management systems (RDBMS), Microsoft Access does not implement database triggers, stored procedures or transaction logging.

    PostgreSQL

    PostgreSQL is object relational database management system (ORDBMS) that supports both relational (SQL) and non-relational (JSON) query. It is a highly stable database that is developed based on the POSTGRES, version 4.2 released by the Computer Science Department in University of Berkeley, California. This database is a popular database or data warehouse for many applications.

    History

    • 1970s – Ingres was being developed in Computer Science department of University of Berkeley as a research project which ended in 1985.

    • 1985 – A post-Ingres project, namely POSTGRES, has been initiated to address the issues in the older database systems. This project aimed to fully support datatypes and define them to establish relationships between the objects of the database. POSTGRES also was able to retrieve data using rules.

    • 1989 – POSTGRES Version 1 was released to the small number of users.

    • 1990 – POSTGRES Version 2 was released again with rewritten rules.

    • 1994 – With the increase in users, POSTGRES Version 4.2 was released, ending the project with it.

    • 1994 – Andrew Yu and Jolly Chen, students of University of Berkeley, replaced the POSTQUEL query language that run POSTGRES, with the SQL language, creating Postgres95.

    • 1995 – Postgres95”s first version was released with a more liberal license.

    • 1996 – The project was renamed to PostgreSQL to support SQL.

    • 1997 – The first version of PostgreSQL version 6.0 was released.

    • Since then, next versions of the project is released as free and open-source software PostgreSQL License.

    • 2000 – PostgreSQL version 7.0 was released on July 8th, 2000. This version had the most changes in features until then. Major changes made were foreign keys were implemented, optimizer had been improved, psql had been updated, and JOIN syntax is now supported.

    • 2005 – The version 8.0 was released on January 19th, 2005. Changes in this version include, the server can now be run on Microsoft natively, savepoints are introduced, Point-in-time recovery, tablespaces, improved buffer managements, checkpoints, changing column types is introduced, etc.

    • 2010 – PostgreSQL version 9.0 was released on 20th September, 2010.

    • 2017 – Version 10.0 was released on 5th October, 2017. Major improvements include: logical replication, declarative table partitioning, improved query parallelism, significant improvements in general performance, etc.

    • 2018 – PostgreSQL version 11.0 was released on 18th October, 2018.

    • 2019 – PostgreSQL version 12.0 was released on 3rd October, 2019.

    • 2020 – PostgreSQL version 13.0 was released on 24th September, 2020.

    • 2021 – PostgreSQL version 14.0 was released on 30th September, 2021.

    • 2022 – PostgreSQL version 15.0 was released on 13th October, 2022.

    Features

    • PostgreSQL supports advanced data types.
    • It possesses high levels of resilience, integrity and correctness of the data.
    • It contains Multi-Version Concurrency Control (MVCC)
    • Point in time recovery
    • Granular Access Controls
    • Asynchronous Replication
    • Nested transactions
    • Online backups
    • Refined query optimizer
    • Write ahead logging
    • It supports international character sets, multi-byte character encodings, Unicode.
    • It is locale-aware for sorting, case-sensitivity, and formatting.
    • Highly scalable.

    SQLite

    SQLite is an embedded relational database system. It is referred to as a lightweight database as it is not more than 500kB in size, which is way less than other relational databases. This database is an in-memory open source library; which is why it can access the files stored in it directly and the source code for it is available in the public domain.

    History

    • 2000 – SQLite 1.0 was released with GNU Database Manager. Dr. Richard Hipp designed it in the same year to create a database that requires no administration.

    • 2001 – SQLite 2.0 was released which contained a custom B-Tree implementation instead of GNU Database Manager, adding transaction capability to the database.

    • 2003 – SQLite 3.0 was released with major improvements like internalization, manifest typing etc.

    • 2011 – Hipp announced his plans to introduce a non-relational interface to SQLite.

    • 2018 – SQLite adopted the Code of Conduct from the Rule of Saint Benedict which was later renamed as Code of Ethics due to some controversies.

    • Despite such controversies, newer versions of SQLite 3.x are being released continuously with better features and improvements.

    • 2023 – The latest version of SQLite is 3.42.0 which was released on 16th July, 2023..

    Features

    • SQLite is an open source library witten in ANSI C.
    • It works on cross-platforms, making it easier to port to other systems.
    • The source code for SQLite is available in public domain.
    • Transactions in SQLite are atomic, consistent, isolated and durable even when the system crashes for any reason.
    • This database does not need any configuration or administration.
    • SQLite is server less, unlike other relational databases. It is linked with application that accesses it. The application directly interacts with the SQLite to read and write on files stored in the disk.
    • It has a simple and easy to use API.
    • In some cases, SQLite is faster than direct filesystem I/O operations.
    • SQLite is self contained, i.e. it is not dependent on any external libraries or operating systems.
    • Uses a stand-alone command line interface (CLI) client to administer SQLite.

    Benefits of Using SQL Database

    Relational databases are the most popular and preferred databases used for data storage. There are many benefits to using SQL database, including:

    Enhanced Flexibility

    Relational databases utilize Data Definition Language (DDL) to modify data stored in tables in real time. The most important thing users can easily add new tables and columns, rename, and implement various changes without disrupting ongoing database operations.

    Data Consistency

    Data Consistency is another important benefit of using SQL databases because it maintains data consistency across applications and server instances.

    Minimum Data Redundancy

    Relational Database Management Systems (RDBMS) use the normalization process to reduce data redundancy. This approach eliminates anomalies in Data stores in the Database.

    Optimized Performance

    Relational databases offer a range of value-added features that boast minimal memory usage, reduced storage costs, and high processor speed.

    Higher Compatibility

    Relational databases offer higher compatibility for integration with modern technologies.

    Scalability

    Higher scalability is another feature that makes Relational databases most preferred.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Overview nhận dự án làm có lương

    SQL – Overview

    Table of content

    What is SQL?

    SQL (Structured Query Language) is a language to operate databases; it includes Database Creation, Database Deletion, Fetching Data Rows, Modifying & Deleting Data rows, etc.

    SQL stands for Structured Query Language which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL was developed in the 1970s by IBM Computer Scientists and became a standard of the American National Standards Institute (ANSI) in 1986, and the International Organization for Standardization (ISO) in 1987.

    Though SQL is an ANSI (American National Standards Institute) standard language, but there are many different dialects of the SQL language like MS SQL Server is using T-SQL and Oracle is using PL/SQL.

    SQL is the standard language to communicate with Relational Database Systems. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their Standard Database Language.

    Why SQL?

    SQL is widely popular because it offers the following advantages −

    • Allows users to access data in the relational database management systems.

    • Allows users to describe the data.

    • Allows users to define the data in a database and manipulate that data.

    • Allows to embed within other languages using SQL modules, libraries & pre-compilers.

    • Allows users to create and drop databases and tables.

    • Allows users to create view, stored procedure, functions in a database.

    • Allows users to set permissions on tables, procedures and views.

    A Brief History of SQL

    • 1970 − Dr. Edgar F. “Ted” Codd of IBM is known as the father of relational databases. He described a relational model for databases.

    • 1974 − Structured Query Language (SQL) appeared.

    • 1978 − IBM worked to develop Codd”s ideas and released a product named System/R.

    • 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.

    • 1987 − SQL became the part of the International Organization for Standardization (ISO).

    How SQL Works?

    When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

    There are various components included in this process. These components are −

    • Query Dispatcher
    • Optimization Engines
    • Classic Query Engine
    • SQL Query Engine, etc.

    A classic query engine handles all the non-SQL queries, but a SQL query engine won”t handle logical files. Following is a simple diagram showing the SQL Architecture −

    SQL Architecture

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – RDBMS Concepts nhận dự án làm có lương

    SQL – RDBMS Concepts

    Table of content

    What is RDBMS?

    RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

    A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd in 1970.

    What is a Table?

    The data in an RDBMS is stored in database objects known as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows.

    Remember, a table is the most common and simplest form of data storage in a relational database. Following is an example of a CUSTOMERS table which stores customer”s ID, Name, Age, Salary, City and Country −

    ID Name Age Salary City Country
    1 Ramesh 32 2000.00 Hyderabad India
    2 Mukesh 40 5000.00 New York USA
    3 Sumit 45 4500.00 Muscat Oman
    4 Kaushik 25 2500.00 Kolkata India
    5 Hardik 29 3500.00 Bhopal India
    6 Komal 38 3500.00 Saharanpur India
    7 Ayush 25 3500.00 Delhi India
    8 Javed 29 3700.00 Delhi India

    What is a Field?

    Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.

    For example, our CUSTOMERS table consists of different fields like ID, Name, Age, Salary, City and Country.

    What is a Record or a Row?

    A record is also called as a row of data is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table −

    ID Name Age Salary City Country
    1 Ramesh 32 2000.00 Hyderabad India

    A record is a horizontal entity in a table.

    What is a Column?

    A column is a vertical entity in a table that contains all information associated with a specific field in a table.

    For example, our CUSTOMERS table have different columns to represent ID, Name, Age, Salary, City and Country.

    What is a NULL Value?

    A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

    It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is the one that has been left blank during a record creation. Following table has three records where first record has NULL value for the salary and second record has a zero value for the salary.

    ID Name Age Salary City Country
    1 Ramesh 32 Hyderabad India
    2 Mukesh 40 00.00 New York USA
    3 Sumit 45 4500.00 Muscat Oman

    SQL Constraints

    Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

    Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table.

    Following are some of the most commonly used constraints available in SQL −

    S.No. Constraints
    1

    Ensures that a column cannot have a NULL value.
    2

    Provides a default value for a column when none is specified.
    3

    Ensures that all the values in a column are different.
    4

    Uniquely identifies each row/record in a database table.
    5

    Uniquely identifies a row/record in any another database table.
    6

    Ensures that all values in a column satisfy certain conditions.
    7

    Used to create and retrieve data from the database very quickly.

    Data Integrity

    The following categories of data integrity exist with each RDBMS −

    • Entity Integrity − This ensures that there are no duplicate rows in a table.

    • Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values.

    • Referential integrity − Rows cannot be deleted, which are used by other records.

    • User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity.

    Database Normalization

    Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process −

    • Eliminating redundant data, for example, storing the same data in more than one table.

    • Ensuring data dependencies make sense.

    Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.

    Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form.

    It is your choice to take it further and go to the Fourth Normal Form, Fifth Normal Form and so on, but in general, the Third Normal Form is more than enough for a normal Database Application.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Home nhận dự án làm có lương

    SQL Tutorial

    SQL Tutorial

    Table of content






    SQL Tutorial

    Our SQL tutorial helps you learn SQL (Structured Query Language) in simple and easy steps so that you can start your database programming quickly. It covers most of the important concepts related to SQL for a basic to advanced understanding of SQL and to get a feel of how SQL works.

    SQL (Structured Query Language) is a programming language which is used to manage data stored in relational databases like MySQL, MS Access, SQL Server, Oracle, Sybase, Informix, Postgres etc.

    SQL is a database computer language designed for the retrieval and management of data in a relational databases like MySQL, MS Access, SQL Server, Oracle, Sybase, Informix, Postgres etc. SQL stands for Structured Query Language. SQL was developed in the 1970s by IBM Computer Scientists.

    SQL is not a database management system, but it is a query language which is used to store and retrieve the data from a database or in simple words SQL is a language that communicates with databases.

    SQL Examples

    Consider we have following CUSTOMERS table which stores customer”s ID, Name, Age, Salary, City and Country −

    ID Name Age Salary City Country
    1 Ramesh 32 2000.00 Maryland USA
    2 Mukesh 40 5000.00 New York USA
    3 Sumit 45 4500.00 Muscat Oman
    4 Kaushik 25 2500.00 Kolkata India
    5 Hardik 29 3500.00 Bhopal India
    6 Komal 38 3500.00 Saharanpur India
    7 Ayush 25 3500.00 Delhi India

    SQL makes it easy to manipulate this data using simple DML (Data Manipulation Language) Statements. For example, if we want to list down all the customers from USA then following will be the SQL query.

    SELECT * FROM CUSTOMERS WHERE country = ''USA

    This will produce the following result:

    ID Name Age Salary City Country
    1 Ramesh 32 2000.00 Maryland USA
    2 Mukesh 40 5000.00 New York USA

    SQL Online Editor

    We have provided SQL Online Editor which helps you to Edit and Execute the SQL code directly from your browser. Try to click the icon run button to run the following SQL code to be executed on CUSTOMERS table and print the records matching with the given condition.

    SELECT * FROM CUSTOMERS WHERE country = ''USA

    So now, you do not need to do a sophisticated setup to execute all the examples given in this tutorial because we are providing you Online SQL Editor, which allows you to edit your code and compile it online. You can try our .

    SQL Basic Commands

    We have a list of standard SQL commands to interact with relational databases, These commands are CREATE, SELECT, INSERT, UPDATE, DELETE, DROP and TRUNCATE and can be classified into the following groups based on their nature −

    Data Definition Language (DDL)

    A Data Definition Language (DDL) is a computer language which is used to create and modify the structure of database objects which include tables, views, schemas, and indexes etc.

    Command Description Demo
    CREATE Creates a new table, a view of a table, or other object in the database.
    ALTER Modifies an existing database object, such as a table.
    DROP Deletes an entire table, a view of a table or other objects in the database.
    TRUNCATE Truncates the entire table in a go. Demo

    Data Manipulation Language (DML)

    A Data Manipulation Language (DML) is a computer programming language which is used for adding, deleting, and modifying data in a database.

    Command Description Demo
    SELECT Retrieves certain records from one or more tables.
    INSERT Creates a record.
    UPDATE Modifies records.
    DELETE Deletes records.

    Data Control Language (DCL)

    Data Control Language (DCL) is a computer programming language which is used to control access to data stored in a database.

    Command Description Demo
    GRANT Gives a privilege to user Demo
    REVOKE Takes back privileges granted from user. Demo

    Why to Learn SQL?

    SQL (Structured Query Language) is a MUST for the students and working professionals to become a great Software Engineer specially when they are working in Software Development Domain. SQL is the most common language used almost in every application software including banking, finance, education, security etc. to store and manipulate data.

    SQL is fairly easy to learn, so if you are starting to learn any programming language then it is very much advised that you should also learn SQL and other Database related concepts to become a complete Software Programmer. There are many good reasons which makes SQL as the first choice of any programmer −

    SQL is the standard language for any Relational Database System. All the Relational Data Base Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

    Also, software industry is using different dialects of SQL, such as −

    • MS SQL Server using T-SQL,

    • Oracle using PL/SQL,

    • MS Access version of SQL is called JET SQL (native format) etc.

    SQL Applications

    SQL is one of the most widely used Query Language over the databases. SQL provides following functionality to the database programmers −

    • Execute different database queries against a database.

    • Define the data in a database and manipulate that data.

    • Create data in a relational database management system.

    • Access data from the relational database management system.

    • Create and drop databases and tables.

    • Create and maintain database users.

    • Create view, stored procedure, functions in a database.

    • Set permissions on tables, procedures and views.

    Who Should Learn SQL

    This SQL tutorial will help both students as well as working professionals who want to develop applications based on some databases like banking systems, support systems, information systems, web websites, mobile apps or personal blogs etc. We recommend reading this tutorial, in the sequence listed in the left side menu.

    Today, SQL is an essential language to learn for anyone involved in the software applicatipon development process including Software Developers, Software Designers, and Project Managers etc.

    Prerequisites to Learn SQL

    Though we have tried our best to present the SQL concepts in a simple and easy way, still before you start learning SQL concepts given in this tutorial, it is assumed that you are already aware about some basic concepts of computer science, what is a database, especially the basics of RDBMS and associated concepts.

    This tutorial will give you enough understanding on the various concepts of SQL along with suitable examples so that you can start your Software Development journey immediately after finishing this tutorial.

    SQL Online Quizzes

    This SQL tutorial helps you prepare for technical interviews and certification exams. We have provided various quizzes and assignments to check your learning level. Given quizzes have multiple choice type of questions and their answers with short explanation.

    Following is a sample quiz, try to attempt any of the given answers:

    Q 1 – The SQL programming language was developed by which of the following:

    Answer : C

    Explanation

    The SQL programming language was developed in the 1970s by IBM researchers Raymond Boyce and Donald Chamberlin.

    Start your online quiz .

    SQL Jobs and Opportunities

    SQL professionals are very much in high demand as the data turn out is increasing exponentially. Almost every major company is recruiting IT professionals having good experience with SQL.

    Average annual salary for a SQL professional is around $150,000. Though it can vary depending on the location. Following are the great companies who keep recruiting SQL professionals like Database Administrator (DBA), Database Developer, Database Testers, Data Scientist, ETL Developer, Database Migration Expert, Cloud Database Expert etc:

    • Google
    • Amazon
    • Netflix
    • Infosys
    • TCS
    • Tech Mahindra
    • Wipro
    • Pinterest
    • Uber
    • Trello
    • Many more…

    So, you could be the next potential employee for any of these major companies. We have developed a great learning material for SQL which will help you prepare for the technical interviews and certification exams based on SQL. So, start learning SQL using our simple and effective tutorial anywhere and anytime absolutely at your pace.

    Frequently Asked Questions about SQL

    There are some very Frequently Asked Questions(FAQ) about SQL, this section tries to answer them briefly.

    SQL skills help software programmers and data experts maintain, create and retrieve information from relational databases like MySQL, Oracle, MS SQL Server etc., which store data into columns and rows. It also allows them to access, update, manipulate, insert and modify data in efficient way.

    A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values.

    There are 5 main types of commands. DDL (Data Definition Language) commands, DML (Data Manipulation Language) commands, and DCL (Data Control Language) commands, Transaction Control Language(TCL) commands and Data Query Language(DQL) commands.

    SQL is very easy to learn. You can learn SQL in as little as two to three weeks. However, it can take months of practice before you feel comfortable using it. Determining how long it takes to learn SQL also depends on how you plan to use it. Following this SQL tutorial will give you enough confidence to work on any software development related to database.

    SQL queries are also more flexible and powerful than Excel formulas and SQL is fast which can handle large amount of data. Unlike Excel, SQL can handle well over one million fields of data with ease.

    Here are the summarized list of tips which you can follow to start learning SQL.

    • First and the most important is to make your mind to learn SQL.
    • Install MySQL or MariaDB database on your computer system.
    • Follow our tutorial step by step starting from very begining.
    • Read more articles, watch online courses or buy a book on SQL to enhance your knowledge in SQL.
    • Try to develop a small software using PHP or Python which makes use of database.

    Following are four basic SQL Operations or SQL Statements.

    • SELECT statement selects data from database tables.
    • UPDATE statement updates existing data into database tables.
    • INSERT statement inserts new data into database tables.
    • DELETE statement deletes existing data from database tables.

    Following are following three SQL data types.

    • String Data types.
    • Numeric Data types.
    • Date and time Data types.

    You can use our simple and the best SQL tutorial to learn SQL. We have removed all the unnecessary complexity while teaching you SQL concepts. You can start learning it now .

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Using DDL Statements nhận dự án làm có lương

    SQL – Using DDL Statements Questions

    1.What is the full form of DDL in Oracle DB?

    1. Data Deleting Language
    2. Data Definition Language
    3. Data Delegating Language
    4. Dummy Data Language

    Answer: B. DDL is one of the categories of SQL which stands for Data Definition Language. Other SQL types are DML, DCL, and TCL.

    2.DDL statements are used for which of the following Oracle database objects?

    1. Tables
    2. Sub-queries
    3. Rows
    4. Columns

    Answer: A. DDL contains commands like CREATE, ALTER and ANALYZE which are used to CREATE TABLEs, view stored subprograms and packages in a database schema.

    3.What is the basic unit of storage in Oracle Database that contains data?

    1. View
    2. Column
    3. Query
    4. Table

    Answer: D. Table is the basic unit of physical storage of data in Oracle database.

    4.Which of the below options best define a View?

    1. It is the shorter form of a table
    2. It is the logical representation of the subsets from one or more tables
    3. It has only one row and one column
    4. None of the above

    Answer: B. View is a query which behaves like a window to format the data contained in one or more tables. Views do not contain any physical data but just a query which are created during runtime.

    5. Which of the following are database objects?

    1. Table
    2. Sequence
    3. Synonym
    4. All of the above

    Answer: D. Objects which are physically stored in database schema are database objects.

    6. Which of the following database objects generate numeric values?

    1. Table
    2. View
    3. Index
    4. Sequence

    Answer: D. Sequence are used to generate unique values starting with a definite value and incremented by a specified factor. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.

    7.Which of the following database objects gives an alternative name to an object?

    1. Synonym
    2. Sequence
    3. View
    4. Index

    Answer: A. A synonym provides a permanent alias for a database object. A public synonym is available to any database user. A private synonym is available only to the user who created it. A synonym is created by using the CREATE SYNONYM command. A synonym is deleted by using the DROP SYNONYM command. Only a user with DBA privileges can drop a public synonym.

    8.Which of the following database objects improves the performance of some queries?

    1. Table
    2. Synonym
    3. View
    4. Index

    Answer: D.

    9. When a table can be created?

    1. When the database is not being used by any user
    2. When the database is newly created
    3. It can be created any time, even when a user is using the database
    4. None of the above

    Answer: C. An index can be created to speed up the query process. DML operations are always slower when indexes exist. Oracle 11g creates an index for PRIMARY KEY and UNIQUE constraints automatically. An explicit index is created with the CREATE INDEX command. An index can be used by Oracle 11g automatically if a query criterion or sort operation is based on a column or an expression used to create the index.

    10. What is true about a table?

    1. It is not mandatory to specify the size of a table
    2. The size of each table is the same
    3. A table can be modified online
    4. None of the above

    Answer: A, C.

    11. A table named 123_A is created for storing the number of employees in an organization. What is wrong in the name of the table?

    1. The name of a table cannot start with a digit
    2. Nothing is wrong in this name.
    3. You cannot use an underscore while naming a table
    4. None of the above

    Answer: A. As per the object naming conventions, table name must start with an alphabet.

    12. What is the range of number of letters a table name can have?

    1. 1-20 characters
    2. 1-10 characters
    3. 1-30 characters
    4. 1-50 characters

    Answer: C. A table name cannot exceed more than 30 characters.

    13 Which of the following characters can be used to name a table?

    1. A to Z
    2. a to z
    3. 0 to 9
    4. All of the above

    Answer: D. As per the standard naming convention in Oracle, object”s name can contain alphabets in any case. Mandatorily, first place is for letters while the rest can be mix of letters and digits.

    14. Which of the following special characters can be used to name a table?

    1. @
    2. #
    3. $
    4. _ (underscore)

    Answer: B, C, D. No other special character, except (#, $, _), are allowed while naming a table. Use of special characters in the table name is discouraged.

    15. What is true about the name of a table?

    1. A table can have a name which is used by some other object owned by the same user
    2. A sequence and a table can have same names
    3. A view and a table can have the same name
    4. A table name must not duplicate the name of another object owned by the same user

    Answer: D. By virtue of namespace, a table name cannot be same as any other schema objects. Schema objects which share the same namespace include tables, views, sequences, private synonyms, stored procedures, stored functions, packages, materialized views, and user-defined types.

    16.You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?

    1. The table will not be created
    2. The table will be created and an underscore will be added automatically to the name COUNT_
    3. An ORA error will be thrown
    4. The table COUNT will be created without any errors

    Answer: A, C. You cannot create a table with the name same as an Oracle Server reserved word.

    17. You create a table using quoted identifiers ” ”. How will you refer this table?

    1. ”table_name”
    2. “table_name”
    3. Either of A or B
    4. None of the above

    Answer: B. If the table is created with the name having a quoted identifier, it must be addressed using double quotes. Using quoted identifiers is not recommended. Quoted identifiers are case-sensitive

    18. You create a table named EMPLOYEES. What among the following is possible?

    1. It can be referred to as eMPLOYEES
    2. It can be referred to as EMPLoyees
    3. It can be referred to as employees
    4. All of the above

    Answer: D. Unquoted objects names are not case-senstive in Oracle.

    19. What among the following are the pre-requisites for creating a table?

    1. CREATE TABLE privilege
    2. Storage space
    3. Data in the table
    4. None of the above

    Answer: A, B. A user must possess the CREATE TABLE privilege and must have sufficient space to allocate the initial extent to the table segment.

    20. What is the syntax for creating a table?

    1. CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,..] );
    2. CREATE TABLE INTO [schema.] table (column datatype [DEFAULT expr] [,..] );
    3. CREATE TABLE VALUES [schema.] table (column datatype [DEFAULT expr] [,..] );
    4. None of the above

    Answer: A.

    21. Pick the element which you must specify while creating a table.

    1. Column name
    2. Column Data type
    3. Column size
    4. All of the above

    Answer: D. A table must have atleasr one column, its data type specification, and precision (if required).

    22. A user named “Kevin” wants to access a table which is owned by another user named “Jonathan”. Which of the following will work for Kevin?

    1. Select * from Kevin.employees;
    2. Select * from jonathan.employees;
    3. Either of A or B
    4. None of the above

    Answer: B.

    23. What is true about a schema?

    1. A schema is owned by a database user and has the same name as that user
    2. Each user owns a single schema
    3. Schema objects include database links
    4. All of the above

    Answer: D. The user space in a database is known as schema. A schema contains the objects which are owned or accessed by the user. Each user can have single schema of its own.

    24. What among the following is true about tables?

    1. A default value is given to a table
    2. A default value can be given to a column of a table during an INSERT statement
    3. Either of A or B
    4. None of the above

    Answer: B. A default value can be specified for a column during the definition using the keyword DEFAULT.

    25. Which of the following can be used with the DEFAULT option while creating a table?

    1. Strings
    2. Expressions
    3. SQL functions
    4. All of the above

    Answer: D. The default value for a column can either be a literal or a derivative using SQL function.

    26. Which of the following command is used to see the structure of a table?

    1. UPDATE
    2. SHOW
    3. DESCRIBE
    4. SPOOL

    Answer: C. DESCRIBE is a SQL*Plus command to list the structure of the table.

    27.What is the limit of CHECK constraints on a column?

    1. No limit
    2. 1
    3. 2
    4. 4

    Answer: A. Oracle imposes no limit on the check constraints on a column.

    28. Which of the following commands will drop table employees? (Consider the table structure as given)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    1. DROP employees
    2. DROP TABLE employees
    3. TRUNCATE employees
    4. None of the above

    Answer: B.

    29. What is true about a namespace?

    1. It is a group of object types
    2. Within a namespace, all the object names should be uniquely identified by schema and name
    3. The same type of objects in different namespaces can share the same name
    4. All of the above

    Answer: D. A namespace defines a group of object types,within which all names must be uniquely identified-by schema and name.Objects in different namespaces can share the same name.

    30. Which of the following object types share the same namespace?

    1. Synonyms
    2. Table
    3. Views
    4. All of the above

    Answer: D.

    31. What among the following is true about a table and an index?

    1. An index and a table can have the same name in a schema
    2. An index and a table within a schema cannot have the same name
    3. Neither of A nor B
    4. None of the above

    Answer: A. As the index and constraints share the same namespace, a table and an index can have the same name.

    32. What is true about creating a table?

    1. While creating a table, each column should be assigned a data type
    2. Data type assignment to columns is not mandatory
    3. A data type has to be assigned to a table and not to a column
    4. None of the above

    Answer: A. Each column must possess behavioral attributes like data types and precision in order to build the structure of the table.

    33. Suppose you create a table as shown below:

    CREATE TABLE employees
(emp_id NUMBER(4),
last_name VARCHAR2 (20)
);

    How much space will Oracle allocate to the LAST_NAME column?

    1. If there are no rows, then Oracle will not allocate any space to the last_name column
    2. If rows are populated then Oracle will allocate unlimited space to the last_name column
    3. Neither of A nor B
    4. None of the above options

    Answer: A.

    34. What is the range of size that a VARCHAR2 data type can take?

    1. 1 byte to 2 byte
    2. 1 byte to 2000 bytes
    3. 1 byte to 4000 bytes
    4. None of the above

    Answer: C. Until Oracle 11g Release 2, string data type VARCHAR2 can maximum contain 4000 bytes.

    35.What is the range of size that a CHAR data type can take?

    1. 1 byte to 2 byte
    2. 1 byte to 2000 bytes
    3. 1 byte to 4000 bytes
    4. 1 byte to 3000 bytes

    Answer: B. Until Oracle 11g Release 2, string data type CHAR can maximum contain 2000 bytes.

    36. What is true about the CHAR data type?

    1. If the data is not the length of the column, then it will be replaced with NULL values
    2. If the data is not the length of the column, then it will be padded with spaces
    3. It is mandatory to have the data of the same size as mentioned in the CHAR size, else it throws an ORA error
    4. None of the above

    Answer: B. CHAR provides a fixed length storage to a value while VARCHAR2 is flexible. If the data of length less than CHAR precision is inserted in a CHAR column, the remaining length will be padded to the column value.

    37. Which of the following is a data type for variable length binary data?

    1. VARCHAR
    2. VARCHAR2
    3. RAW
    4. NVARCHAR2

    Answer: C.

    38. What is the precision allowed for the NUMBER data type?

    1. 1 to 20
    2. 1 to 4K
    3. 1 to 30
    4. 1 to 38 digits

    Answer: D. Until Oracle 11g Release 2, primary data type NUMBER had the maximum precision of 38 digits.

    39. What is the scale allowed for the NUMBER data type?

    1. 1 to 20
    2. -84 to 100
    3. -84 to 127
    4. None of the above

    Answer: C.

    40. Which of the following are the data types for date and time data?

    1. TIMESTAMP
    2. INTERVAL DAY TO SECOND
    3. TIMESTAMP WITH LOCAL TIMEZONE
    4. All of the above

    Answer: D.

    41. Which of the following data types are for large objects?

    1. CLOB
    2. BLOB
    3. RAW
    4. All of the above

    Answer: A, B. LOB data types in SQL are BLOB, CLOB, and BFILE.

    42. What will happen if the inserted value is of a smaller length as defined for a VARCHAR2 data type column?

    1. It will throw an ORA error
    2. It will get inserted successfully and the value will take up as much space as it needs.
    3. It will get inserted and the remaining space will be padded with spaces
    4. None of the above

    Answer: B. VARCHAR2 contains variable length character data.

    43. What does NUMBER (8, 2) in oracle mean?

    1. It means there are 8 digits in total, 6 digits before the decimal and 2 after the decimal
    2. It means there are 10 digits in total with 8 digits before the decimal and 2 after decimal
    3. It means there are 2 digits before the decimal and 8 after the decimal point
    4. None of the above

    Answer: A. The p indicates precision,the total number of digits to the left and right of the decimal position, to a maximum of 38 digits; the s, or scale, indicates the number of positions to the right of the decimal.Example: NUMBER(7, 2) can store a numeric value up to 99999.99. If precision or scale isn”t specified, the column defaults to a precision of 38 digits.

    44. Which of the following queries will create a table with no rows in it?

    1. CREATE TABLE emp AS SELECT 0 from dual;
    2. CREATE TABLE emp AS SELECT * from employees where 1=1;
    3. CREATE TABLE emp AS SELECT * from employees where 1=2;
    4. CREATE TABLE emp AS SELECT 0 from employees;

    Answer: C. The direct path operation CTAS (CREATE TABLE .. AS SELECT..) can be used to copy the structure of an existing table without copying the data.

    45. Which of the following statements would add a column to a table already created?

    1. ALTER TABLE table_name add column (job varchar2(20));
    2. ALTER TABLE table_name add job varchar2(20);
    3. ALTER TABLE table_name add (job varchar2(20));
    4. ALTER TABLE table_name add column (job);

    Answer: C. The ALTER TABLE command allows a user to add a new column to a table.The same rules for creating a column in a new table apply to adding a column to an existing table.The new column must be defined by a column name and datatype (and width, if applicable).A default value can also be assigned. The difference is that the new column is added at the end of the existing table-it will be the last column.

    46. Which of the following statements will modify the data type of an already existing column?

    1. ALTER TABLE table_name MODIFY (job varchar2(10) );
    2. ALTER TABLE table_name MODIFY job varchar2(10);
    3. ALTER TABLE table_name MODIFY column (job varchar2(10) );
    4. ALTER TABLE table_name MODIFY (job varchar2(10) );

    Answer: A. The ALTER TABLE..MODIFY is used to modify column definition in a table. The admissible changes are increasing column precision, change datatype within a datatype family, or change the default value of the column.

    47. Which of the following statements will remove a column from the table?

    1.  ALTER TABLE table_name DROP (job varchar2(10) );
    2. ALTER TABLE table table_name DROP COLUMN (job varchar2(10) );
    3. ALTER TABLE table table_name DROP COLUMN (job);
    4. ALTER TABLE table_name MODIFY (job varchar2(10) );

    Answer: C. The ALTER TABLE..DROP COLUMN can be used to drop a column from the table.

    48. Which of the following will rename the column emp_id to empno?

    1. ALTER TABLE employees RENAME column emp_id to empno;
    2. ALTER TABLE employees RENAME emp_id to empno;
    3. ALTER TABLE employees RENAME column emp_id to empno;
    4. None of the above;

    Answer: A. The ALTER TABLE..RENAME can be used to rename an existing column in teh table.

    49. You need to mark the table employees as read only. Which of the following statements will you execute to get the required result?

    1. ALTER TABLE employees set READ;
    2. ALTER TABLE employees READ ONLY;
    3. ALTER TABLE employees READ_ONLY;
    4. ALTER TABLE employees set READ ONLY;

    Answer: B. A table can be marked read only to make it passive against the DML and DDL statements. The read only feature was introduced in Oracle 11g.

    50. What among the following is true about DDL statements?

    1. DDL commands become the part of ongoing transaction
    2. DDL commands are auto commit and end the ongoing active transaction
    3. If the DDL command fails, the current transaction is still committed
    4. If the DDL command fails, the current transaction is rolled back

    Answer: B. DDL commands are auto commit only if they are successfully executed without errors. If DDL command fails, the ongoing transaction is still active in the session and not committed into the database.

    51. What happens if there is an active transaction against a table on which a DDL is issued?

    1. The transaction rolls back
    2. The transaction is committed and terminated
    3. Both A and B
    4. None of the above

    Answer: B.

    52. Which of the following commands will remove unused columns in an SQL statement?

    1. ALTER TABLE tablename DROP COLUMN column_name;
    2. ALTER TABLE tablename DROP unused columns;
    3. ALTER TABLE tablename set unused column;
    4. ALTER TABLE tablename DROP columns;

    Answer: C. The SET UNUSED command drops only the un-used columns from a table and is faster

    53. What happens when a table which is marked Read Only is attempted for drop?

    1. It will throw an error
    2. It will no longer remain Read Only but cannot be dropped either
    3. It will be dropped without errors
    4. It will remain un-touched

    Answer: C. The DROP command affects the data dictionary definition of the tables which are not Read Only and hence dropping is possible

    Consider the following statement and answer the questions 54 and 55 that follow:

    CREATE TABLE departments
(dept_id NUMBER (2),
 dept_name VARCHAR2(14),
 create_date DATE DEFAULT SYSDATE);

    54. What will happen if the DEFAULT clause specification is removed from the statement?

    1. The script will throw error because DATE columns must be specified with a default value
    2. A system generated default value will be assigned to the column
    3. Table will be created with no default value for CREATE_DATE column
    4. None of the above

    Answer: C.

    55.What is true about the above statement?

    1. It will automatically commit the transaction in session
    2. It will create the table DEPARTMENTS in the schema
    3. It will set a default value for CREATE_DATE column
    4. None of the above

    Answer: A, B, C.

    56. Up to which limit can a BLOB data type column hold values?

    1. 1 KB
    2. 2 GB
    3. 4 GB
    4. 3 KB

    Answer: C. As per Oracle 11g, the maximum size of data accomodated in a BLOB can be 4GB.

    57.What is the difference between CLOB and BLOB data types? (Choose the most appropriate answer)

    1. CLOB is character data , BLOB is binary data
    2. CLOB is character data up to 2GB, BLOB is binary data up to 4 GB
    3. CLOB is character data up to 4 GB, BLOB is binary data up to 4 GB
    4. None of the above

    Answer: C. CLOB is a character large object which is used to store character files like PDF, docs and text files while BLOB is a binary LOB used to store media files.

    58.What among the following is a ROWID?

    1. It is a serial number given to a set of rows starting with 1
    2. It is an alphanumeric address given to a row in a table
    3. Both A and B
    4. None of the above

    Answer: B. It is a base-64 system representing the unique address of a row in its table.

    59.What is the data type used for storing Binary data stored in an external file (up to 4 GB)?

    1. BLOB
    2. CLOB
    3. CFILE
    4. BFILE

    Answer: D. BFILE is an external LOB type which is used to refer external media files. Internal LOB types are BLOB and CLOB which are used for binary large files and character large files stored in the database.

    60. What is true about a table created with a sub-query?

    1. A VARCHAR2 data type column is not copied when a table is created using a sub-query
    2. A CLOB data type column is not copied when a table is created using a sub-query
    3. A LONG column is not copied when a table is created using a sub-query
    4. None of the above

    Answer: C. The CTAS method to create a table doesn”t copies the LONG column.

    61. Which of the following data types cannot be used with a GROUP BY and an ORDER BY clause?

    1. CLOB
    2. VARCHAR2
    3. CHAR
    4. LONG

    Answer: D. LONG data types cannot be used in GROUP BY and ORDER BY clause.

    62. How many LONG columns can a table contain?

    1. None
    2. Maximum 2
    3. Minimum 2
    4. Only one

    Answer: D. A table can contain maximum one column of LONG type.

    63.Which of the following data types cannot be constrained in SQL?

    1. VARCHAR2
    2. LONG
    3. CHAR
    4. DATE

    Answer: B. Constraints cannot be created on LONG type columns.

    64. Which of the following data types can you use if you want a date with fractional seconds?

    1. DATE
    2. VARCHAR2
    3. TIMESTAMP
    4. None of the above

    Answer: C. The TIMESTAMP data type provides additional precised information of date values. It provides fractional seconds and time zone information.

    65. You need to store an interval of days, hours, minutes and seconds in a column. Which of the data type would help?

    1. TIMESTAMP
    2. INTERVAL YEAR TO MONTH
    3. INTERVAL DAY TO SECOND
    4. None of the above

    Answer: C.

    66.You need to find how many employees were hired in June, 2011 and June, 2012. Which of the following data types will help?

    1. INTERVAL DAY TO SECOND
    2. TIMESTAMP
    3. DATE
    4. INTERVAL YEAR TO MONTH

    Answer: D.

    67. What is true about constraints?

    1. They enforce rules at the row level
    2. They enforce rules at the table level
    3. It is mandatory to have constraints created while creating a table
    4. None of the above

    Answer: B. A constraint is a rule applied to data being added to a table. It represents business rules, policies, or procedures.Data violating the constraint isn”t added to the table.A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table with the ALTER TABLE command. A constraint based on composite columns (more than one column) must be created by using the table-level approach.

    68. How are constraints helpful?

    1. They limit the storage capacity of a table and hence save DB space
    2. They prevent the modification of a table
    3. They prevent deletion of a table if there are dependencies
    4. None of the above

    Answer: C. A constraint is a rule applied to data being added to a table.It represents business rules, policies, or procedures.Data violating the constraint isn”t added to the table.

    69.A RAW data type column can store variable-length binary strings up to what value?

    1. 10 GB
    2. 1 TB
    3. 2 GB
    4. 4 GB

    Answer: C.

    70. Which of the following are valid constraints in Oracle?

    1. INDEX
    2. GENERAL
    3. UNIQUE
    4. PRIMARY KEY

    Answer: C, D. A NOT NULL constraint can be created only with the column-level approach. A PRIMARY KEY constraint doesn”t allow duplicate or NULL values in the designated column. Only one PRIMARY KEY constraint is allowed in a table. A FOREIGN KEY constraint requires that the column entry match a referenced column entry in the table or be NULL. A UNIQUE constraint is similar to a PRIMARY KEY constraint, except it allows storing NULL values in the specified column. A CHECK constraint ensures that data meets a given condition before it”s added to the table.

    71. Which of the below DML operations consider constraints on a column?

    1. INSERT
    2. UNION
    3. DELETE
    4. UPDATE

    Answer: A, C, D. All the DML operations obey constraints on the columns of the table.

    72. When can a constraint be created?

    1. While creating a table
    2. After creating a table
    3. Both A and B
    4. None of the above

    Answer: C. A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table with the ALTER TABLE command.

    73 Where are constraints stored?

    1. In the SGA
    2. In a table
    3. In data dictionary
    4. None of the above

    Answer: C.

    74. You create a constraint but do not name it. What will be the default name given to the constraint?

    1. SYS_Cn
    2. SYS_constraint
    3. SYS_Const
    4. SYS_C0

    Answer: A. By default, Oracle gives a generic name to the constraints SYS_Cn, where the n is an integer to keep the name of a constraint unique.

    75. What is the functional difference between a column-level constraint and a table-level constraint?

    1. Column-level constraint applies to all the columns of a table
    2. Table-level constraint applies to all the columns of a table
    3. They both are functionally the same, only the syntax is different
    4. None of the above

    Answer: C. Functionally, the table level constraints and column level constraints work similar. Composite constraints can be defined at table level only.

    76. What is true about column-level constraints?

    1. They can be created before the creation of a table
    2. They can be created before the defining of a column
    3. They are included when the column is defined
    4. None of the above

    Answer: C. Column level constraints are defined along with the column specification.

    77. What is true about NOT NULL constraints in SQL?

    1. They should be defined at the table level
    2. They should be defined at the column level
    3. They should be defined only on one column
    4. They should be defined only on one row

    Answer: B. A NOT NULL constraint can be created only with the column-level approach.

    Consider the following statement and answer the questions 78 and 79 that follow:

    CREATE TABLE employees (
emp_id NUMBER (6)  CONSTRAINT emp_emp_id_PK PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
hire_date DATE
);

    78.Which type of constraint is created in the above statement?

    1. Column level constraint
    2. Table level constraint
    3. Named constraint
    4. Specification constraint

    Answer: A. A column level constraint is created along with the column definition.

    79. What modification can be made to the above statement to give it a table level constraint?

    1. CONSTRAINT emp_emp_id_PK PRIMARY KEY
    2. CONSTRAINT emp_emp_id_PK PRIMARY KEY (EMP_ID)
    3. CONSTRAINT emp_emp_id_PK EMP_ID PRIMARY KEY
    4. CONSTRAINT PRIMARY KEY emp_emp_id_PK

    Answer: B.

    80. What is true about PRIMARY KEY constraint?

    1. It applies a NOT NULL constraint implicitly to the column on which it is defined
    2. It applies a UNIQUE KEY constraint implicitly to the column on which it is defined
    3. It applies a CHECK constraint implicitly to the column on which it is defined
    4. It applies a DEFAULT constraint implicitly to the column on which it is defined

    Answer: A. A PRIMARY KEY constraint doesn”t allow duplicate or NULL values in the designated column. Only one PRIMARY KEY constraint is allowed in a table.

    81. What among the following is true regarding a UNIQUE KEY constraint?

    1. UNIQUE KEY constraint and PRIMARY KEY constraint are the same
    2. UNIQUE KEY constraint allows NULL values if there is no NOT NULL defined on the column(s)
    3. We can have two identical rows when a UNIQUE KEY constraint is defined on a column
    4. None of the above

    Answer: B. A UNIQUE constraint is similar to a PRIMARY KEY constraint, except it allows storing NULL values in the specified column.

    Consider the following statement and answer the questions 82 and 83 that follow:

    CREATE TABLE employees (
emp_id NUMBER (6)
first_name VARCHAR2(20),
last_name VARCHAR2(20),
job VARCHAR2(20),
hire_date DATE
CONSTRAINT emp_job_UK UNIQUE (job));

    82. Which of the below statements interpret the above CREATE TABLE script?

    1. This table cannot have two identical Job IDs
    2. This table can have two or more identical Job IDs
    3. This table can have NULL values in the JOB column
    4. None of the above

    Answer: A, C. A UNIQUE constraint on the JOB column will restrict duplicate value but allows nulls.

    83. If the constraint emp_job_UK is modified as emp_job_PK PRIMARY KEY (job), what will be outcome?

    1. This change can happen only if there”s no NULL value in the JOB column
    2. This change can happen without any restrictions
    3. This change will change the values of the column JOB
    4. None of the above

    Answer: A.

    84. What is true about the UNIQUE key constraint?

    1. A unique key index is implicitly created when a UNIQUE constraint is defined on a column
    2. A PRIMARY KEY constraint is implicitly created when a UNIQUE constraint is defined on a column
    3. A NOT NULL constraint is implicitly created when a UNIQUE constraint is defined on a column
    4. None of the above

    Answer: A. When a unique constraint is imposed on a table, Oracle internally creates a unique key index on the column to restrict the duplication of values.

    85. Which of the following is true about indexes?

    1. If an UPDATE statement is executed on a table, the indexes need to be manually updated as well
    2. If a DELETE statement is executed on a table, the indexes need to manually deleted as well
    3. When a table is dropped, the indexes are automatically dropped
    4. If an UPDATE statement is executed on a table, the corresponding indexes are updated as well.

    Answer: C, D.

    86.Which of the following CREATE TABLE statements is valid?

    1. CREATE TABLE EMPLOYEES
(emp_id NUMBER (2) PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL); 
    2. CREATE TABLE EMPLOYEES
(emp_id NUMBER (2) PRIMARY KEY NOT NULL,
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL PRIMARY KEY); 
    3. CREATE TABLE EMPLOYEES
(emp_id NUMBER (2) PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL UNIQUE);
    4. CREATE TABLE EMPLOYEES
(emp_id NUMBER (2),
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL,
CONSTRAINT emp_emp_id_PK PRIMARY KEY (emp_id));

    Answer: A, C, D. All the CREATE TABLE scripts are valid.

    87. How many PRIMARY KEY constraints can a table have?

    1. 0
    2. Unlimited
    3. 2
    4. 1

    Answer: D. A table can have one and only one primary key.

    88. You want to put a CHECK constraint on the EMP_ID such that it should be equal to the current value of a Sequence through which it is getting its values. Which of the following statements will help you achieve this?

    1. Emp_id NUMBER (10) CONSTRAINT emp_emp_id_chk CHECK (emp_id = EMPNO.CURRVAL);
    2. Emp_id NUMBER (10) CONSTRAINT emp_emp_id_chk CHECK (emp_id = EMPNO.NEXTVAL);
    3. Emp_id NUMBER (10) CONSTRAINT emp_emp_id_chk CHECK (EMPNO.CURRVAL);
    4. None of the above

    Answer: D. You cannot use CURRVAL, NEXTVAL, LEVEL and ROWNUM pseudo columns in the CHECK constraint

    89. Which of the following commands will help in converting the foreign key values to NULL?

    1. ON DELETE CASCADE
    2. ON DELETE SET NULL
    3. CASCADE
    4. REFERENCES

    Answer: B.

    90. You need to add a constraint to the EMPLOYEES table which restricts the addition of those employees who have salaries less than 10000. Which of the following commands will give you the required results?

    1. ALTER TABLE employees ADD CONSTRAINT emp_emp_sal_CHECK CHECK (salary >= 10000); 
    2. ALTER TABLE employees ADD CHECK CONSTRAINT emp_emp_sal_CHECK (salary>10000); 
    3. ALTER TABLE employees ADD CONSTRAINT CHECK emp_emp_sal_CHECK (salary = 10000); 
    4. ALTER TABLE employees ADD CONSTRAINT emp_emp_sal_CHECK (salary < 10000);

    Answer: A.

    91. You need to add a constraint to the EMPLOYEES table which imposes a restriction that the HIRE_DATE for all the employees should be equal to SYSDATE-7. Which of the following statements will give you the required results?

    1. ALTER TABLE employees ADD CHECK CONSTRAINT emp_emp_sal_CHECK  ( to_char(hire_date,''DD-MON-YY'') = SYSDATE -7); 
    2. ALTER TABLE employees ADD CONSTRAINT CHECK emp_emp_sal_CHECK ( to_char(hire_date,''DD-MON-YY'') = SYSDATE -7); 
    3. ALTER TABLE employees ADD emp_emp_sal_CHECK CHECK ( to_char(hire_date,''DD-MON-YY'') = SYSDATE -7);
    4. None of the above

    Answer: D. You cannot use SYSDATE, UID, USER and USERENV functions in the CHECK constraint.

    Consider the following query and answer the questions 92 to 94 that follow:

    CREATE TABLE EMPLOYEES
(emp_id NUMBER (2),
first_name VARCHAR(20),
last_name VARCHAR(20),
dept_id NUMBER (10),
hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_emp_id_PK PRIMARY KEY (emp_id, hire_date)
CONSTRAINT emp_dept_FK FOREIGN KEY (dept_id)
REFERENCES departments (dept_id)
);

    92. Which of the below statements interpret the CREATE TABLE script?

    1. A FOREIGN KEY constraint is defined at the table level on the column DEPT_ID
    2. The FOREIGN KEY constraint defined references the DEPT_ID from the DEPARTMENTS table
    3. Both A and B
    4. None of the above

    Answer: C. The keywords FOREIGN KEY and REFERENCES are used when we define a FOREIGN KEY constraint for referential integrity.

    93. You need to delete all the dependent rows in DEPARTMENTS table when you delete the EMPLOYEES table. Which of the following command will solve the purpose? (Consider the table structures as given)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    1. ON DELETE SET NULL
    2. ON DELETE CASCADE
    3. DELETE ALL
    4. FOR UPDATE

    Answer: B. If ON DELETE CASCADE is included in the constraint definition and a record is deleted from the parent table,any corresponding records in the child table are also deleted automatically.

    94. The EMPLOYEES table as shown below, has 5 employees who work in department 10. An executive from admin department issues the below query.

    DELETE FROM departments
WHERE dept_id = 10;

    What will be the outcome of this query? (Assume the table structures as shown)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    1. Integrity constraint error
    2. Successful execution
    3. Neither of A nor B
    4. None of the above

    Answer: A. The DEPT_ID from DEPARTMENTS is the foreign key in the table EMPLOYEES and there are employees in department 10 ,hence a value cannot be deleted from the parent table unless the child record is found.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Manipulating Data nhận dự án làm có lương

    SQL – Manipulating Data Questions

    1.What does ACID mean with respect to relational database?

    1. Accuracy, Consistency, Isolation, Database
    2. Accuracy, Concurrency, Isolation, Durability
    3. Atomicity, Consistency, Isolation, Durability
    4. Atomicity, Concurrency, Isolation, Durability

    Answer: C. All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. Atomicity states that all tasks of a transaction are performed or none of them are. There are no partial transactions. Consistency implies the transaction takes the database from one consistent state to another consistent state. Isolation means the effect of a transaction is not visible to other transactions until the transaction is committed. Durability means that changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

    2. What does the word DML stands for in Oracle SQL?

    1. Durability Management Language
    2. Database Management Language
    3. Database Manipulation Language
    4. None of the above

    Answer: C. DML stands for Data Manipulation Language.

    3. Which of the following are DML commands in Oracle Database?

    1. SELECT
    2. GROUP BY
    3. INTERSECT
    4. INSERT

    Answer: A, D. On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables.

    4.Which of the following DML commands can be considered to be a hybrid of INSERT and UPDATE in a single statement?

    1. INTERSECT
    2. INSERT
    3. SELECT
    4. MERGE

    Answer: D. MERGE can perform INSERT and UPDATE actions in a single statement in Oracle.

    5. What all operations can MERGE statement perform in SQL?

    1. INSERT
    2. DELETE
    3. GROUP BY
    4. None of the above

    Answer: A, B. In some conditions MERGE can perform the DELETE operation too, along with INSERT and UPDATE.

    6.Which of following commands is a DDL (Data Definition Language) command but is often considered along with DML commands?

    1. DELETE
    2. INSERT
    3. TRUNCATE
    4. None of the above

    Answer: C. TRUNCATE is a DDL command. It removes the records from the table without any condition. It is not the part of any ongoing transaction and an uncommitted transaction in the session is committed after TRUNCATE is executed.

    7.Which of the following commands manipulate data basically?

    1. MINUS
    2. UPDATE
    3. TRUNCATE
    4. All of the above

    Answer: B, C. UPDATE is a DML statement to modify a column value in a table. TRUNCATE manipulates the data by removing them unconditionally from a table.

    8. Which of the following commands is used to populate table rows with data?

    1. DELETE
    2. INSERT
    3. SELECT
    4. UPDATE

    Answer: B. INSERT command is used to insert rows in a table.

    9. What is true about the INSERT statement? (Choose the most appropriate answer)

    1. It can insert data in one row of one table at a time
    2. It can insert data in many rows of one table at a time
    3. It can insert data in many rows of many tables at a time
    4. All of the above

    Answer: C. The INSERT statement is capable of inserting a row or set of rows in a single table at a time.

    10.What is true about the insertion of rows in tables?

    1. The rows can be inserted randomly
    2. Any number of rows can be inserted in a table without any restrictions
    3. Generally the rows are inserted in a table based on certain rules known as constraints
    4. All of the above

    Answer: C. Constraints are business rules imposed on the columns so as to ensure the behavior of the data coming in the column. These constraints are validated for the data during the INSERT process.

    11. What is true about the INSERT statement in Oracle SQL? (Choose the most appropriate answer)

    1. An INSERT statement can override any constraint put on the table
    2. An INSERT statement cannot be used on a table if a constraint is already placed on the table
    3. An INSERT statement can be used on a table only if a constraint is already placed on the table
    4. An INSERT statement can never insert a row that violates a constraint.

    Answer: D. Oracle raises exception if any of the data contained in the insert statement violates the constraint.

    Consider the following data set from the EMPLOYEES table along with its structure and answer the questions 12, 13 and 14: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER (6)
 FIRST_NAME			  VARCHAR2 (20)
 LAST_NAME		 NOT NULL VARCHAR2 (25)
 EMAIL			 NOT NULL VARCHAR2 (25)
 PHONE_NUMBER			  VARCHAR2 (20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2 (10)
 SALARY 			  NUMBER (8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE             CLERK
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

    12. Examine the structure of the EMPLOYEES table. You issue the following command:

    INSERT INTO EMPLOYEES (employee_id  , first_name , job_id) VALUES (5100, ''BRUCE'', ''CLERK'');

    Assuming that there is a duplicate value check constraint on the EMPLOYEE_ID column, what will be the outcome of the above statement?

    1. It will insert another row with 5100 BRUCE CLERK values
    2. It will insert another row with 51002 BRUCE CLERK values
    3. It will throw a ”Constraint violated” ORA error
    4. None of the above

    Answer: C. As the row with values “5100, BRUCE, CLERK” already exists in the table, the insert statement with same data set is not possible.

    13.You issue the following command to the data set shown above:

    INSERT INTO EMPLOYEES (employee_id  , first_name , job_id) VALUES (51003,''BRUCE'',''CLERK'');

    What will be the output of this statement?

    1. It will insert a new row with 51003 BRUCE CLERK values
    2. It will throw an ORA error as there cannot be another BRUCE who is a CLERK
    3. It will throw an ”Constraint violated” ORA error
    4. None of the above

    Answer: A. As there is no constraint on the columns FIRST_NAME and job_id, the INSERT will work without any error

    14. You issue the following command to the data set shown above:

    INSERT INTO EMPLOYEES (employee_id  , first_name , job_id ) VALUES (51003,''BRUCE'', NULL);

    What will be the output of this statement?

    1. It will insert a new row with 51003 BRUCE CLERK values
    2. It will throw an ORA error as there cannot be another BRUCE who is a CLERK
    3. It will throw an ”Constraint violated” ORA error
    4. It will insert a new row with 51003 BRUCE NULL values

    Answer: D. As there is no NOT NULL constraint on the columns FIRST_NAME and JOB_ID , the NULL value will get inserted.

    15. What among the following can be said regarding inserting of rows in tables?

    1. The user cannot interactively insert rows
    2. A query can be written with substitution variables for an interactive insertion of rows by the users
    3. When a user is prompted for inserting rows, the insert doesn”t work and it throws an ORA error
    4. None of the above

    Answer: B. An INSERT statement can make use of substitution variable to prompt the user to key in values during the runtime. It provides an interactive way of inserting data into tables

    16.Which of the following can be used to insert rows in tables?

    1. SELECT
    2. INSERT
    3. Sub-queries
    4. All of the above

    Answer: D. INSERT statement can make use of explicit INSERT, INSERT-SELECT or a sub-query method to insert data into tables.

    17. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)

    1. Using SELECT clause
    2. Manually typing each value into the INSERT clause
    3. Using SET operators
    4. None of the above

    Answer: A. Using the SELECT clause is the most common technique for inserting rows into tables. It reduces the effort of manually keying in values for each column.

    18.Which of the following commands is used to change the rows that already exist in a table?

    1. INSERT
    2. UNION
    3. UPDATE
    4. SELECT

    Answer: C. UPDATE is a DML statement which is used to modify the column values in a table.

    19.What is true about the UPDATE command?

    1. It can update only one row at a time
    2. It can update only 100 rows at a time
    3. It can update unlimited rows at a time in bulk
    4. None of the above

    Answer: C. An UPDATE can update multiple rows in one or more rows at a time based on the WHERE clause conditions.

    20.Which of the following clauses decides how many rows are to be updated?

    1. SELECT
    2. WHERE
    3. FROM
    4. All of the above

    Answer: B. UPDATE statement makes use of WHERE clause to capture the set of rows which needs to be updated.

    21.What among the following is true about the UPDATE statement? (Choose the most appropriate answer)

    1. An UPDATE can update rows from only one table
    2. An UPDATE can update rows from multiple tables
    3. A single UPDATE command cannot affect rows in multiple tables
    4. None of the above

    Answer: A, C. An UPDATE statement affects rows of only one table and not multiple tables.

    Consider the following data set from the EMPLOYEES table and its structure. Answer questions 22 to 24 that follow. 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE             CLERK
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

    22. You need to change the JOB_ID for Bruce (Employee Id 7389) to ”ACCOUNTANT”. Which of the following statements will you fire?

    1. UPDATE employees
SET job_id  = ''ACCOUNTANT''
WHERE employee_id   = 7389;
    2. INSERT INTO EMPLOYEES (employee_id  , first_name , job_id ) VALUES (5100,''BRUCE'', ''ACCOUNTANT'');
    3. UPDATE employees
SET job_id  = ''ACCOUNTANT''
WHERE job_id  = ''CLERK
    4. UPDATE employees
SET job_id  = ''ACCOUNTANT

    Answer: A. Option B fails because it modifies the job code of all clerks to ACCOUNTANT. Option C is wrong because it update job code to ACCOUNTANT for all the employees in the table.

    Answer the following questions 23 and 24 based on the below actions –

    You issue the following query to the EMPLOYEES table with the data set as shown above.

    UPDATE employees
Set job_id  = NULL
Where employee_id   = 51000;

    The data set will be as shown below: (Assume that there is a duplicate value constraint on the EMPLOYEE_ID column)

    EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

    23. Suppose you fire an UPDATE statement to update Bruce”s JOB_ID to ”SALESMAN” (with respect to the data set shown above). What will be the outcome of the query?

    1. Bruce”s job code will still be NULL
    2. Bruce”s job code will be modified to ”SALESMAN”
    3. ORA error
    4. No action

    Answer: B. The UPDATE will add the new value to the NULL value changing the NULL to the new value

    24. You issue an UPDATE statement to update the employee id 7389 to 7900. You query the employee by its id ”7389” before committing the transaction. What will be the outcome?

    1. Update will work successfully while select will show 7389.
    2. Update will work successfully while select will show no records.
    3. Constraint on EMPLOYEE_ID will not allow it to be updated
    4. It will update successfully but all the values for the EMPLOYEE_ID 7389 will become NULL.

    Answer: B. A query in a session is consistent with the ongoing transactions. If the same query would have been executed in a different session, it would have shown the employee record with id 7389 because the active transaction in the first session is not yet committed.

    25. What among the following is a typical use of an UPDATE statement? (Select the most appropriate answer)

    1. To retrieve a row and update one of more columns of that row
    2. To modify all the rows for some columns
    3. To modify all the rows for all the columns of a table
    4. None of the above

    Answer: A. Although, the UPDATE statement can modify all column values in all rows, but typically it is used to select a row and update one or more columns.

    26. Which of the following practices appropriately describe for selecting which row set to update using the UPDATE statement?

    1. If some rows are to be updated, PRIMARY KEY constraint can be used
    2. If all rows are to be updated, WHERE clause can be considered
    3. To update a set of rows use WHERE, to update all rows of a table, put a PRIMARY KEY constraint on the table
    4. None of the above

    Answer: C.

    27. Which of the following columns in a table are not usually updated?

    1. LONG type columns in the table
    2. LOB columns in the table
    3. A primary key column which also serves as foreign key reference in another table
    4. All of the above

    Answer: C. As a common practice, the primary key columns which serve as foreign key reference in other tables, should not be updated. Though they can be updated by deferring the constraints which is usually not recommended.

    Consider the following data set and structure of the EMPLOYEES table and answer the questions 28 and 29 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE             NULL
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

    28. You issue an UPDATE statement as follows:

    UPDATE employees
SET job_id  = NULL;

    What will be the outcome of the above statement?

    1. The first row of the data set will get updated to NULL
    2. The 3rd column of the first row will get updated to NULL
    3. The 3rd column of all the rows will get updated to NULL
    4. And ORA error will be thrown

    Answer: C. An UPDATE statement without a WHERE clause will update all the rows of the table.

    29. You issue an UPDATE statement as follows:

    UPDATE employees
SET employee_id   = NULL;
WHERE job_id  = ''CLERK

    What will be the outcome of the above statement? (Here the column EMPLOYEE_ID is marked as mandatory by putting a constraint)

    1. The first column of the data set will get updated to NULL
    2. The 3rd column of the first row will get updated to NULL
    3. The 3rd column of all the rows will get updated to NULL
    4. And ORA error will be thrown

    Answer: D. The constraints on the column must be obeyed while updating its value. In the given UPDATE statement, error will be thrown because the EMPLOYEE_ID column is a primary key in the EMPLOYEES table which means it cannot be NULL.

    30. Which of the following commands can be used to remove existing records from a table?

    1. UPDATE
    2. INSERT
    3. MINUS
    4. DELETE

    Answer: D. DELETE is used to remove the records from the table which can be optionally based upon a condition. Being a DML statement, it is the part of a transaction.

    31. What among the following is true about the DELETE statement?

    1. The DELETE statement has to be accompanied by the WHERE clause
    2. It is not mandatory to write a WHERE clause with the DELETE statement
    3. DELETE can remove data from multiple tables at a time
    4. None of the above

    Answer: B. The WHERE clause predicate is optional in DELETE statement. If the WHERE clause is omitted, all the rows of the table will be deleted.

    32.What among the following happens when we issue a DELETE statement on a table? (Choose the most appropriate answer)

    1. A prompt pops up asking the user whether he/she is sure of deleting the rows requested
    2. The rows obeying the condition given in the DELETE statement are removed immediately
    3. The requested rows are removed immediately without any prompt.
    4. None of the above

    Answer: C. As a part of the active or a new transaction, the rows in the table will be deleted.

    33.Consider the following data set from the EMPLOYEES table and its structure:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
     EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

    You need to delete the data from the JOB_ID column in the row with employee_id 51001. Which of the following queries will be correct?

    1. UPDATE employees
SET job_id  = NULL
WHERE employee_id   = 51001;
    2. DELETE job_id  FROM employees
WHERE employee_id   = 51001;
    3. DELETE FROM employees;
    4. None of the above

    Answer: D. You cannot delete a particular column value for a particular row with the DELETE statement. The entire row gets deleted based on the conditions given. Unwanted values in a column can be updated to NULL. Option ”A” is near but not correct in the context of the question.

    34. What is the difference between the UPSERT and MERGE statements?

    1. There is no difference
    2. UPSERT is the latest term adopted for MERGE statement, which has turned obsolete
    3. UPSERT can perform delete operation which MERGE cannot
    4. MERGE does INSERT, UPDATE and DELETE, UPSERT does only UPDATE and INSERT

    Answer: D. UPSERT is an obsolete statement and MERGE took over with new capabilities.

    35. What is the difference between the MERGE command and the commands INSERT, UPDATE and DELETE?

    1. MERGE statement consumes more time than each operation (INSERT, UPDATE, DELETE) done separately
    2. MERGE is obsolete after Oracle 10g
    3. MERGE can perform all three operations on a table while INSERT, UPDATE and DELETE perform one operation at a time.
    4. None of the above.

    Answer: C. The MERGE statement can embed all three operations on a table in a single statement while INSERT, UPDATE and DELETE perform one operation at a time.

    36. Which of the following objects can be the data source in a MERGE statement?

    1. A table only
    2. A sub-query only
    3. A table or a sub-query
    4. Both A or B

    Answer: C. MERGE works well with a table or a subquery.

    37. What among the following is a TRUNCATE statement equivalent to? (Choose the most suitable answer)

    1. To a DELETE statement
    2. To an UPDATE statement
    3. A DELETE statement without a WHERE clause
    4. None of the above

    Answer: C. TRUNCATE deletes all the rows in one command.

    38.Which of the following situations indicate that a DML operation has taken place?

    1. When new rows are added to a table
    2. When two queries are combined
    3. When a table is truncated
    4. None of the above

    Answer: A. When existing rows in a table are inserted, modified or removed from a table, it is done through a DML statement.

    39.Which of the following best defines a transaction?

    1. A transaction consists of DDL statements on the database schema
    2. A transaction consists of COMMIT or ROLLBACK in a database session
    3. A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session
    4. A transaction consists of collection of DML and DDL statements in different sessions of the database

    Answer: C. A database transaction consists of one or more DML statements to constitute one consistent change in data, or a DDL statement or a DCL command (GRANT or REVOKE). It starts with the first DML statement and ends with a DCL or DDL or TCL (COMMIT or ROLLBACK) command. Note that DDL and DCL commands hold auto commit feature.

    40. What does a collection of DML statements that form a logical unit work known as?

    1. ACID property
    2. UNION
    3. UNION ALL
    4. Transaction

    Answer: D.

    41.What happens when a DML statement in an active transaction encounters an error on execution?

    1. The complete transactions is rolled back
    2. The DMLs in the transaction are mutually exclusive and hence can continue their execution
    3. The other DMLs in the transactions are interrupted and wait until the error is resolved
    4. None of the above

    Answer: A. If any of the DML statement in an active transaction encounters error, the whole transaction ends up in a rollback.

    42.What is true about the keyword VALUES in INSERT statements?

    1. VALUES can add multiple rows at a time during the INSERT
    2. VALUES can add only 100 rows at a time during the INSERT
    3. VALUES is mandatory to be used if we use the keyword INSERT
    4. VALUES add only one row at a time

    Answer: D. The VALUES keyword is used only when the column values are explicitly specified in the INSERT statement.

    Consider the following statement and the table structure. Answer the questions 43 to 45 that follow: 

    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    INSERT INTO departments (department_id , department_name , manager_id, location_id )
VALUES (100, ''Human Resources'', 121, 1000);

    43. How many rows will be inserted by the above statement?

    1. 0
    2. 2
    3. 3
    4. 1

    Answer: D. When the keyword VALUES is used, it inserts only one row at a time.

    44. In which order the values will get inserted with respect to the above INSERT statement?

    1. Location_id , manager_id, department_name , department_id
    2. department_id , department_name , manager_id, location_id
    3. department_id , manager_id, department_name , location_id
    4. department_id , department_name , location_id , manager_id

    Answer: B. If the columns are mentioned in the INSERT clause, the VALUES keyword should contain values in the same order

    45. Suppose the above given statement is modified as below:

    INSERT INTO departments VALUES (100, ''Human Resources'', 121, 1000);

    What will be the outcome of this modification? Assume that the DEPARTMENTS table has four columns namely, department_id ,DEPARTMENT_NAME ,MANAGER_ID and LOCATION_ID .

    1. It will insert values into all the columns of the departments table assuming that column values are provided in the same sequence as the column in the table
    2. It will throw an ORA error because column names are not explicitly mentioned
    3. It will throw an ORA error because VALUES clause is wrongly used in the INSERT
    4. None of the above

    Answer: A. Including the column names in the INSERT statement is optional provided the values must comply with the count and sequence of the columns in the table.

    46. What will be the outcome of the below INSERT statement? (Consider the table structure)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    INSERT INTO EMPLOYEES (employee_id , hire_date) VALUES (210,"21-JUN-2013");
    1. It will insert only the employee_id and the hire date of the employee, leaving all other columns as blanks
    2. It will insert only the employee_id
    3. It will throw an ORA error
    4. None of the above

    Answer: C. The date literal formatting contains error. It should be enclosed within single quotation marks and not double quotation marks.

    47.What will be the outcome of the below INSERT statement? (Consider the given table structure)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    INSERT INTO EMPLOYEES (employee_id , first_name) VALUES (210,"Bryan");
    1. It will insert only the employee_id and the first name of Bryan, leaving all other columns as blanks
    2. It will insert only the first name
    3. It will throw an ORA error
    4. None of the above

    Answer: C. The string literal formatting contains error. It should be enclosed within single quotation marks and not double quotation marks.

    48. Suppose you need to insert the name O”Callaghan as the last name of the employees table. Which of the following queries will give you the required results? (Consider the given table structure)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    1. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,''O''callahan''); 
    2. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,''O"callahan''); 
    3. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,''O'' ''Callahan''); 
    4. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,"O''callahan");

    Answer: C.

    49. What will be the outcome of the below INSERT statement? (Consider the given table structure)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    INSERT INTO EMPLOYEES (employee_id , first_name) VALUES ("210",''Bryan'');
    1. It will throw a numeric value error
    2. It will insert only the employee_id and the first name of Bryan, leaving all other columns as NULL
    3. It will insert only the employee_id
    4. None of the above

    Answer: A. Number values should not be enclosed within quotes.

    50. What will be the outcome of the below INSERT statement? (Consider the given table structure)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    INSERT INTO departments VALUES (200,''Accounts'', NULL, NULL);
    1. It will throw an ORA error
    2. It will execute successfully but with wrong values might get inserted in the columns
    3. It will execute successfully
    4. None of the above

    Answer: C. NULLs can be used in the VALUES clause to fill up the column values alternatively.

    51. What will be the outcome of the below INSERT statement? (Assume there is a NOT NULL constraint on the department_id column and consider the table structure given)

    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    INSERT INTO departments VALUES (NULL, ''Accounts'', NULL);
    1. It will throw an ORA error
    2. It will execute successfully but with wrong results
    3. It will execute successfully but with correct results
    4. None of the above

    Answer: A. NULL values cannot be inserted into non null columns.

    52. What will be the outcome of the below INSERT statement? (Assume there is a NOT NULL constraint on the department_id column and consider the given table structure)

    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    INSERT INTO departments VALUES (200, 34, NULL);
    1. It will execute successfully but with wrong results
    2. It will throw an ORA error
    3. It will execute successfully but with correct results
    4. None of the above

    Answer: B. Data type of the value mismatches with the data type of the column in the table.

    53. Which of the following commands is used to save the changed data in a table permanently?

    1. ROLLBACK
    2. COMMIT
    3. INSERT
    4. UPDATE

    Answer: B. The TCL command COMMIT is used to end the current active transaction in a session by making all the pending data changes permanent in the tables.

    54. Which of the following commands allows undoing the changed data?

    1. ROLLBACK
    2. COMMIT
    3. INSERT
    4. UPDATE

    Answer: A. The TCL command ROLLBACK is used to end the current active transaction in a session by discarding all the pending data changes.

    55. Which of the following commands allows enabling markers in an active transaction?

    1. ROLLBACK
    2. COMMIT
    3. SAVEPOINT
    4. None of the above

    Answer: C. SAVEPOINT marks a point in a transaction which divides the transaction into smaller sections.

    56. Which of the following commands prevents other users from making changes to a table?

    1. ROLLBACK
    2. COMMIT
    3. LOCK TABLE
    4. SAVEPOINT

    Answer: C.

    57. What is true about an INSERT statement which tries to insert values into a virtual column? (Choose the most appropriate answer)

    1. It cannot insert values in the Virtual column
    2. It can insert values
    3. It throws an ORA error
    4. All of the above

    Answer: A. A Virtual column is a column which is always auto generated based on the derivation expression defined in the column specification. Its value cannot be explicitly inserted by the user.

    58.Which of the following commands allows the user to insert multiple rows with a single statement?

    1. INSERT
    2. INSERT ALL
    3. UNION ALL
    4. None of the above

    Answer: B. Bulk insert operations can be carried out using INSERT ALL.

    59. Which of the following is the syntax for inserting rows through a sub-query?

    1. INSERT INTO tablename [{column_name,..}]
subquery; 
    2. INSERT INTO tablename  VALUES [{column_name,..}]
subquery;
    3. Both A and B
    4. None of the above

    Answer: A.

    Consider the following exhibit of the EMPLOYEES table and answer the questions 60 to 63 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

    60. Which of the following queries will execute successfully?

    1. UPDATE employees
SET salary = salary + 1000
WHERE to_char (hire_date, ''YYYY'') > ''2006 
    2. UPDATE employees
SET salary = salary + 1000
WHERE to_date (hire_date, ''YYYY'') > ''2006 
    3. UPDATE employees
SET salary = salary + 1000
WHERE hire_date > to_date (substr (''01-jan-200'',8)); 
    4. UPDATE employees
SET salary = salary + 1000
WHERE hire_date in (to_date (''JUN 01 11'', to_date (''JUL 01  11''));

    Answer: A.

    61.Due to structural reorganization in the organization, you are asked to update department IDs for all the employees to NULL before the final decision is made public. Only those records should be updated which have the JOB_ID as NULL. Which of the following queries will work?

    1. UPDATE employees
SET department_id  = NULL
Where job_id  = NULL; 
    2. UPDATE employees
SET department_id  = NULL
Where job_id  = TO_NUMBER(NULL); 
    3. UPDATE employees
SET department_id  = NULL
Where job_id  IS NULL; 
    4. UPDATE employees
SET department_id  = TO_NUMBER ('' '', 9999)
Where job_id  = TO_NUMBER(NULL);

    Answer: C. Use IS NULL operator to check column value for nullity.

    62.You need to add a basic employee data into EMPLOYEES table. The basic data contains the last name as ”Bond” and department ID as 300. Which of the following statements will give the correct results?

    1. INSERT INTO employees (employee_id , last_name, department_id )
(100,''Bond'',
(select department_id  from departments where department_id  = 300));  
    2. INSERT INTO employees (employee_id , last_name, department_id )
VALUES (100,''Bond'',
(select department_id  from departments where department_id  = 300));
    3. INSERT INTO employees (employee_id , last_name, department_id )
VALUES (''100'',''Bond'',300);
    4. None of the above

    Answer: B, C. Sub queries do work in INSERT statements provided they return a scalar value of data type matching or compatible to the column for which they are used.

    63. You fire the following query: 

    DELETE FROM EMPLOYEES;

    Assuming that there are no active transactions on the EMPLOYEES table in any sessions, which of the following statements is true?

    1. It removes all the rows and structure of the table
    2. It removes all the rows which can be rolled back
    3. It removes all the rows permanently
    4. None of the above

    Answer: B. Being a DML statement, the data changes due to DELETE operation are made permanent only after COMMIT is issued in the session.

    64.Consider the structure of the COUNTRY table as shown: 

    SQL> desc countries
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 COUNTRY_ID		 NOT NULL CHAR(2)
 COUNTRY_NAME			  VARCHAR2(40)
 REGION_ID			  NUMBER

    You issue the following statements in a session.

    INSERT INTO COUNTRIES (1, ''Whales'')
/
INSERT INTO COUNTRIES (2, ''England'')
/
SAVEPOINT A;
UPDATE COUNTRIES
SET country_id= 100 where country_id= 1
/
SAVEPOINT B;
DELETE FROM COUNTRIES where country_id= 2
/
COMMIT
/
DELETE FROM COUNTRIES where country_id= 100
/

    What will happen when a ROLLBACK TO SAVEPOINT command is issued for the user session?

    1. The rollback generates an error
    2. Only DELETE statements are rolled back
    3. No SQL statement is rolled back
    4. None of the above

    Answer: A, C. Since there are two savepoints – A and B, and the ROLLBACK command does specifies the actual savepoint mark, Oracle throws error.

    65.If a user issues a DML command and exits the SQL Developer abruptly without a COMMIT or ROLLBACK, what will be the outcome? (Assume the session is not auto commit)

    1. Automatic COMMIT
    2. Automatic ROLLBACK
    3. Might be a COMMIT or a ROLLBACK to end the transaction
    4. None of the above

    Answer: B. When transaction is interrupted by a system failure, the entire transaction is automatically rolled back.

    66. Which of the following commands / statements would end a transaction?

    1. COMMIT
    2. SELECT
    3. SAVEPOINT
    4. CREATE

    Answer: A, D. Apart from TCL commands i.e. COMMIT or ROLLBACK, the DDL commands and DCL commands possess auto commit feature. The active transaction will be committed if the DDL statement is executed in the same session.

    67.When does a transaction complete?

    1. When a ROLLBACK is executed
    2. When a COMMIT is executed
    3. When TRUNCATE is executed
    4. All of the above

    Answer: D. Transaction completes if a TCL, DCL or a DDL command is executed in the session.

    68. Examine the given table structures and consider the following query:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    INSERT INTO EMPLOYEES (department_id ) VALUES
(select department_id  FROM departments);

    What will be the outcome of the above query?

    1. The columns in the EMPLOYEES table and the departments table do not match
    2. The WHERE clause is mandatory to be used in a sub-query
    3. The VALUES keyword cannot be used with the INSERT clause when sub-queries are used
    4. None of the above

    Answer: C. Wrong usage of VALUES keyword. It must be used only when you have column data in hand, which has to be inserted in the table.

    69.Examine the given table structure and consider the following query: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
    UPDATE (select employee_id , job_id  from employees)
SET hire_date = ''01-JAN-13''
WHERE employee_id  = (select employee_id  FROM job_history);

    Which of the following is true regarding the given query?

    1. It would not execute as we cannot use two tables in a single update statement
    2. It would not execute as UPDATE cannot use a sub-query
    3. It would execute with the restrictions on the column specified
    4. It would not execute as sub-query is used in the WHERE clause

    Answer: C.

    70.What happens when a transaction is committed?

    1. The changes made during the transaction are saved for a particular user session
    2. The changes made during the transaction are discarded
    3. If the transaction is a DDL, the commit doesn”t work
    4. None of the above

    Answer: D. Committing a transaction saves the pending data changes permanently into the database.

    71. Which of the following reasons will the best one on the usage of string?

    1. Using sub-queries
    2. Syntax errors
    3. Access permissions
    4. Constraint violations

    Answer: C, B, D. References to non-existing objects / columns, Space issues might be other reasons.

    72. What happens when an INSERT statement tries to insert records in an old table?

    1. All the columns will get NULL values
    2. A new table with the same name would get created automatically and the values would get inserted
    3. INSERT cannot work and it throws an ORA error
    4. None of the above

    Answer: C.

    73. A user named ”Jonathan Adams” is able to SELECT columns from the EMPLOYEES table but he is unable to insert records into EMPLOYEES. What can be the reason?

    1. Jonathan is connected to a database which does not support INSERT statements
    2. The INSERT statement cannot be applied on the table EMPLOYEES
    3. Jonathan has access to SELECT but no access to INSERT INTO the table EMPLOYEES
    4. None of the above

    Answer: C. Users can enjoy table access based on their responsibilities. One can have only read access on a table while other can enjoy read and write access.

    74. Suppose 1 million rows are to be inserted into the AUDIT table. An INSERT transaction runs successfully for the first 1000 rows and an ORA error is thrown ”Constraint violated”. What will happen to the values inserted in the first 1000 rows?

    1. They will remain as it is
    2. They all will get deleted
    3. They all will get rolled back
    4. None of the above

    Answer: C. If any of the DML statement during the transaction encounters error(s), the complete transaction will be rolled back.

    Examine the table structure and consider the following query and answer the questions 75, 76 and 77 that follow: 

    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    INSERT INTO departments values (15, NULL);

    75. What will be the outcome of this statement?

    1. It will insert a row with department_id = 15 and all the other values as NULL
    2. It will execute successfully but insert 0 rows in the table
    3. It will throw an ORA error as the no. of columns and values do not match
    4. None of the above

    Answer: C. The DEPARTMENTS table contains four columns but the INSERT statement supplies value for two columns only without mentioning the columns too. Hence, the ORA error is thrown.

    76. What is true about the above INSERT statement?

    1. If the columns are not mentioned in the INSERT statement, the values are inserted positionally in the columns
    2. It is mandatory to mention columns after the INSERT statement
    3. Both A and B
    4. None of the above

    Answer: A. If the columns are not specified in the INSERT statement, Oracle sequentially and positionally maps each value to the column in the table.

    77. With respect to the statement given above, what will happen if the table is altered to add a new column?

    1. The statement will still work
    2. The statement execution will throw an error as there will be a mismatch in the no. of columns and values
    3. There will be no change and the statement will execute as before
    4. None of the above

    Answer: B. Since the columns were not specified earlier, the problem will still exist. Mismatch in the column-value mapping would throw an ORA error.

    Examine the table structure given below and consider the following queries and answer the questions 78 and 79 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    Query 1: 
    INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, ''ADAMS'',''21-DEC-12'');
    Query 2: 
    INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, upper(''ADAMS''),to_date(''21-DEC-12'',''DD-MON-YY''));

    78. Which of the above two queries is better?

    1. Both are better
    2. Only Query 1 is better
    3. Only Query 2 is better
    4. None of the queries is correct

    Answer: C. Query-2 is better because it inserts date value as a date and not as a string. Though Oracle will perform implicit conversion of string literal specified as a date, but not recommended.

    79. Which of the following queries is equivalent of the query 2 given above?

    1. INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (101-1, upper(''ADAMS''),to_date(''21-DEC-12'',''DD-MON-YY''));

    2. INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (99+1, upper(''ADAMS''),to_date(''22-DEC-12'',''DD-MON-YY'') +1 );

    3. INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, upper(''ADAMS''),to_date(''21-DEC-12'',''DD-MON-YY'') - 1);

    4. INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, upper(''ADAMS''),to_date(''28-DEC-12'',''DD-MON-YY'')-7 );

    Answer: A, C, D. Arithmetic operations /functions can be used to insert values as shown above.

    80. You need to copy the data from one table to another table. Which of the following methods can be used?

    1. You can use the COPY command
    2. You can use the INSERT command
    3. You can use the UPDATE command
    4. None of the above

    Answer: B. The direct path operations INSERT-AS-SELECT (IAS) is the most commonly used method to copy data from one table to another.

    81.Which of the following statements will copy data from the JOB_HISTORY table to the JOB_HISTORY_ARCHIVE table? (Consider the table structure as given)

    SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
    1. INSERT INTO job_history values (select * from job_history);
    2. INSERT INTO JOB_HISTORY_ARCHIVE values (select * from job_history_archive);
    3. INSERT INTO JOB_HISTORY_ARCHIVE select * from job_history;
    4. None of the above

    Answer: C. The option ”C” correctly shows the usage of IAS (INSERT-AS-SELECT) method.

    Examine the given table structure. Consider the following query and answer the questions 82 and 83 that follow:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    INSERT ALL
WHEN job_id   = ''SA_REP'' then
INTO employees (employee_id , department_id , salary, hire_date)
VALUES (employee_id , 10, salary, hire_date)
WHEN job_id  <> ''SA_REP'' then
INTO employees (employee_id , department_id  , salary, hire_date)
VALUES (employee_id , 20, salary, hire_date)
SELECT employee_id , department_id , job_id, salary, commission_pct , hire_date
FROM employees
WHERE hire_date > sysdate - 30;

    82. Interpret the output of the above INSERT statement.

    1. Thrown an error
    2. It will insert the records for all the employees who were hired a month before the sysdate.
    3. It will insert the records for all the employees who are Sales Representatives in department 10
    4. None of the above

    Answer: B, C. INSERT ALL can make conditional inserts into the target tables.

    83. Which employees” data will be inserted in the department 20?

    1. Sales Representatives
    2. Accountants
    3. Both A or B
    4. None of the above

    Answer: B. As per the INSERT ALL statement, the details of employees whose job_id is not ”Sales Representative”.

    84. What will be the outcome of the below query? (Consider the table structure as given)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    INSERT INTO employees (employee_id , salary) VALUES (&employee_id , &salary);
COMMIT;
    1. Syntax error as substitution variables cannot be used in DML statements
    2. The user will be prompted for entering the employee ID and the salary but substitution variables cannot insert data in the table
    3. The user will be prompted for entering the employee ID and the salary and record will be successfully created in the EMPLOYEES table
    4. None of the above

    Answer: C. Substitution variables work well with the DML statements.

    85. Evaluate the following SQL statements that are executed in a user session in the specified order:

    CREATE SEQUENCE id_seq;
SELECT id_seq.nextval
FROM dual;

INSERT INTO employees (employee_id ,first_name,job_id )
VALUES (ord_seq.CURRVAL, ''Steyn'',''Trainee'');

UPDATE employees
SET employee_id = id_seq.NEXTVAL
WHERE first_name = ''Steyn''
AND job_id =''Trainee

    What would be the outcome of the above statements?

    1. The CREATE SEQUENCE command would throw error because the minimum and maximum value for the sequence have not been specified
    2. All the statements would execute successfully and the employee_id column would contain the value 2 for the employee STEYN.
    3. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
    4. All the statements would execute successfully and the employee_id column would have the value 20 for the employee STEYN because the default CACHE value is 20.

    Answer: B.

    86. What is the restriction on the sub-query used in the UPDATE statement?

    1. The sub-query should be a multi row sub-query
    2. The sub-query should be a single row sub-query
    3. There”s no restriction
    4. The sub-query can be either a single row or a multi row sub-query

    Answer: B. The sub-query should not return multiple rows when being used in an UPDATE statement

    Examine the given table structure and consider the query given below and answer the questions 87 and 88 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id =7382);

    87. What will be the outcome of the above query?

    1. It throws an ORA error on execution
    2. Salary of all the employees will be updated with the same salary as the employee 7382
    3. Salary of all the employees will be updated to NULL
    4. None of the above

    Answer: B. Query results can be used to update the column values in a table.

    88. Suppose if the employee 7382 doesn”t exist in the EMPLOYEES table. What will be the outcome of the query?

    1. It throws an ORA error on execution because query results cannot be updated to the columns
    2. Salary of all the employees will be updated to NULL
    3. ORA exception ”NO_DATA_FOUND” will be raised because employee 7382 doesn”t exists
    4. None of the above

    Answer: B. UPDATE statements do not raise any exception except for syntactical errors.

    Examine the given table structure and consider the query given below and answer the questions 89 and 90 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    UPDATE employees
set salary = (select salary from employees where last_name = ''Adams'');

    89. What will be the outcome of the query?

    1. It executes successfully
    2. All the rows of the table have the same salary
    3. It might throw an ORA error ”TOO_MANY_ROWS” upon execution
    4. None of the above

    Answer: C. The sub-query might return more than one row causing an error.

    90. What changes in the above query will make sure there are no errors caused?

    1. Use a single row function like MAX, MIN or AVG to reduce multi row results into a scalar result
    2. Adding a Primary key constraint on SALARY column
    3. No change required
    4. None of the above

    Answer: A.

    Examine the given table structure and consider the following query and answer the questions 91 and 92 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    UPDATE employees
SET salary = (select max (salary) from employees where last_name = ''Adams'');

    91. What will be the outcome of the query given above?

    1. It will update the salaries of all the employees equal to the salary of the employee named Adam
    2. It will update the salaries of all the employees equal to the average salary of all with last name as ”Adam”
    3. It will update 0 rows
    4. It will update only one row

    Answer: B. Arithmetic functions MAX or a MIN can be used with sub-queries to get scalar values and avoid errors.

    92. Assume that the sub-query above is replaced with the following:

     SELECT distinct salary from employees where last_name = ''Adam

    What will be the outcome of the main query given above?

    1. It will execute successfully giving incorrect results
    2. It will execute successfully giving correct results
    3. It will throw an ORA error
    4. None of the above

    Answer: C. it gives an error because as there are many with the last name as ”Adam” there will many distinct salaries.

    Examine the given table structure and consider the following query and answer the questions 93 and 94 that follow: 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    UPDATE employees
SET salary = 50000;
WHERE job_id  in (select job_id  from job_history where department_id  = 10);

    93. What will the above statement do? (Choose the most appropriate answer)

    1. It will update all the salaries for all the employees as 50000
    2. It will update all the salaries for all the employees who are in department 10
    3. It will update the salaries for all the employees who have one of the job IDs similar to those in department 10
    4. None of the above

    Answer: C.

    94. What will happen if the WHERE clause given above is replaced with the following?

     WHERE job_id = (select job_id from job_history where department_id  = 10);
    1. It will execute successfully with incorrect results
    2. It will execute successfully with correct results
    3. It will throw an ORA error
    4. None of the above

    Answer: C. The equal sign will raise the error.

    Examine the given table structure and consider the following statement. Answer the questions 95 to 97 that follow. 

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    DELETE FROM employees where last_name = ''A%
COMMIT;

    95. What will be the outcome of the query given above?

    1. Executes successfully but no rows are deleted
    2. All the employees whose last_name starts with “A” will be deleted
    3. ORA error because DELETE statement cannot have WHERE predicate
    4. All the rows from the employees table will get deleted

    Answer: A. DELETE statement can have WHERE clause predicate. Based on conditions, the records will be removed from the table.

    96. Consider the following statement:

    DELETE FROM employees where employee_id  IS NULL and job_id = NULL;
COMMIT;

    Assuming there is a NOT NULL constraint on the column employee_id , what will be the outcome of the above query?

    1. It will raise ORA error because of invalid WHERE predicates
    2. It will execute successfully and no rows will be deleted
    3. It will raise ORA error because multiple predicates cannot work in DELETE statements
    4. None of the above

    Answer: B. Multiple predicates can be applied to the DML statements UPDATE and DELETE.

    97. Consider the following query:

    DELETE FROM employees where department_id  = &deptID;
COMMIT;

    What will happen when the above statement is executed?

    1. It will raise error because DML statements cannot use substitution variables
    2. It will prompt for the department ID to be deleted from the user and delete the record with the given department ID
    3. It will prompt for the department ID but transaction cannot be committed
    4. None of the above

    Answer: B. Substitution variables can be used with DML statements.

    98. All parts of a transaction should complete or none of them. Which property of ACID rule complies with the given statement?

    1. Atomicity
    2. Consistency
    3. Isolation
    4. Durability

    Answer: A. ACID refers to the basic properties of a database transaction: Atomicity, Consistency, Isolation, and Durability. Atomicity implies that entire sequence of actions must be either completed or aborted. Consistency implies that the transaction takes the resources from one consistent state to another. Isolation implies that a transaction”s effect is not visible to other transactions until the transaction is committed. Durability implies that the changes made by the committed transaction are permanent and must survive system failure.

    99. What does the principle of Durability in the ACID property state?

    1. It states that a database can lose completed transactions
    2. It states that a transaction cannot get completed
    3. It states that once a transaction completes, it must be impossible for the DB to lose it.
    4. None of the above

    Answer: C.

    100. An incomplete transaction should be invisible to all the other users. Which of the properties of the ACID state this?

    1. Isolation
    2. Consistency
    3. Atomicity
    4. Durability

    Answer: A. “I” stands for Isolation.

    101. What does the principle of consistency states?

    1. It states that the results of a query must be consistent with the state of the DB at the time the query started
    2. It states that an incomplete transaction should be invisible to all the other users
    3. It states that once a transaction completes, it must be impossible for the DB to lose it
    4. None of the above

    Answer: A. the “C” in ACID property stands for Consistency

    102. What among the following best describes a Transaction?

    1. INSERT to COMMIT/ROLLBACK
    2. UPDATE to COMMIT/ROLLBACK
    3. DELETE to COMMIT/ROLLBACK
    4. INSERT/UPDATE/DELETE to COMMIT/ROLLBACK

    Answer: D.

    103. A user named “Jonathan” inserts data in the table EMPLOYEES. When will the other users be able to see the new data?

    1. When Jonathan provides access permission to the users
    2. When Jonathan executes a ROLLBACK statement in the session
    3. When Jonathan executes a COMMIT statement in the same session
    4. When Jonathan opens a new session and issues a COMMIT

    Answer: C. The active transaction must be committed in the same session.

    104. What can be said about the nesting of transactions?

    1. Maximum 2 levels of nesting are possible
    2. Maximum 255 levels of nesting are possible
    3. Nesting of a transaction is impossible
    4. Only 1 level of nesting is possible

    Answer: C.

    105. Which of the following reasons will terminate a transaction?

    1. A DDL statement
    2. Exiting a client
    3. System crashes
    4. All of the above

    Answer: D. DDL is auto commit and will end the ongoing active transaction.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQL – Creating Other Schema Objects nhận dự án làm có lương

    SQL – Creating Other Schema Objects Questions

    1. Which database object among the following provides a layer of abstraction between the users and the data?

    1. Table
    2. Rows
    3. Views
    4. Synonyms

    Answer: C, D. Views and Synonyms do not store data themselves. A view is a temporary or virtual table used to retrieve data stored in underlying database tables.

    2. Which of the following data base objects can generate serial numbers?

    1. Synonyms
    2. Views
    3. Tables
    4. Sequences

    Answer: D. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.

    3. What is true about views?

    1. They are equal to tables
    2. They store data from one or many tables
    3. We can execute SELECT and other DMLs on Simple views
    4. Views share the same namespace as tables and hence a table and a view cannot have the same name

    Answer: C, D. DML operations aren”t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.

    4. Why are views useful? (Choose the most appropriate answer)

    1. Because they have shorter names than tables
    2. To prevent users from accessing the columns of tables
    3. To simplify user SQL
    4. All of the above

    Answer: B, C. A view is a temporary or virtual table used to retrieve data stored in underlying database tables. The view query must be executed each time the view is used. A view can be used to simplify queries or restrict access to sensitive data.

    5. In which of the below scenarios, DML operations on a view are not possible?

    1. View contains GROUP BY clause
    2. Base tables contain NOT NULL columns but not selected in the view query
    3. View query uses ROWNUM pseudocolumn
    4. All of the above

    Answer: D. DML operations aren”t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.

    6. Where can views get their data from?

    1. Tables from the same schema
    2. Tables from different schema
    3. Both A and B
    4. None of the above

    Answer: C.

    Consider the given table structure and the following statement and answer the questions 7 to 9 that follow:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    CREATE VIEW emp_details AS
SELECT hire_date, job, salary, department_id FROM employees;

    7. You issue the below query. How many columns will the user see as a result of the below query?

    SELECT * FROM emp_details WHERE department_id= 100;
    1. 0
    2. 1
    3. 2
    4. 4

    Answer: D. Since the view definition is based on four columns from the EMPLOYEES table, a query on a view with all column will show those four columns only.

    8. You need to get the department name in addition to the above 4 columns. Which of the following query will give you the required results?

    1. SELECT E.*, dept_name
FROM departments D join emp_details E
ON (E.department_id= D.dept_id);
    2. SELECT hire_date, job, salary, dept_name FROM emp_details
    3. This is not possible a view cannot be joined to other tables
    4. None of the above

    Answer: A. A view can be joined with other tables or views in a SELECT query.

    9. You need to find the maximum salary along with the department name in addition to the 4 columns selected in the view. Which of the following query will give you the required results?

    1. Select dept_name, e.salary
FROM departments D join emp_details E
On (E.department_id= D.dept_id);
    2. Select dept_name, max(salary)
FROM departments D join emp_details E
On (E.department_id= D.dept_id)
Group by dept_name;
    3. View cannot appear in queries using group functions
    4. Select dept_name, max(salary)
FROM departments D join emp_details E
On (E.department_id= D.dept_id);

    Answer: B.

    10. What among the following is true about synonyms?

    1. PUBLIC and PRIVATE synonyms can have the same name for the same table
    2. DROP SYNONYM will remove a synonym
    3. DROP PUBLIC SYNONYM can be executed only by a SYSDBA
    4. None of the above

    Answer: A, C. A synonym can be a private synonym, which users use to reference objects they own,or a public synonym, which users use to access another user”s database objects. Only SYSDBA or a user with DBA privileges can create a public synonym.

    11. What is true about creating a view? (Choose the most appropriate answer)

    1. A view can only be created from a table
    2. A view can only be created from one table
    3. A view can be created from one or many tables or views
    4. None of the above

    Answer: C. A view containing expressions or functions or joining multiple tables is considered a complex view. A complex view can be used to update only one table.

    12. Which of the following privileges are required to create views in one”s own schema?

    1. CREATE TABLE system privilege
    2. CREATE VIEW system privilege
    3. ALTER VIEW system privilege
    4. CREATE ANY VIEW system privilege

    Answer: B. CREATE VIEW privilege is required by a user to create a view in its own schema.

    13. Which of the following privileges are required to create views in someone else”s schema?

    1. CREATE ANY VIEW
    2. CREATE VIEW
    3. Both A and B
    4. None of the above

    Answer: A. CREATE ANY VIEW privilege is required by a user to create a view in other user”s schema.

    14.Which of the following are supported for an object view or relational view?

    1. LOBs
    2. Object types
    3. REF data types
    4. All of the above

    Answer: D.

    15. What among the following are different types of Views?

    1. Simple views
    2. Complex views
    3. Both A and B
    4. None of the above

    Answer: C. Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn”t include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

    16. What is true about a simple view?

    1. DML statements can be issued most of the times against simple views
    2. There is only one source base table
    3. No group functions are used
    4. All of the above

    Answer: D. Simple views are based on a subquery that references only one table and doesn”t include group functions, expressions, or GROUP BY clauses.

    17.What is true about a complex view?

    1. DML statements cannot be issued against complex views
    2. Contain multiple base tables
    3. Aggregations cannot be performed
    4. All of the above

    Answer: D. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

    18.Which keyword combination should be used to implicitly drop a view (if it exists) and create a new view with the same name?

    1. CREATE VIEW
    2. REPLACE VIEW
    3. CREATE OR REPLACE VIEW
    4. None of the above

    Answer: C. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view”s previous version should be replaced with the one defined in the new command.

    19.How is a view stored in the data dictionary?

    1. As a WHERE clause
    2. As a CREATE statement
    3. As an UPDATE statement
    4. As a SELECT statement

    Answer: D.

    20.Which of the following can contain single-row functions?

    1. Inline Views
    2. Simple Views
    3. Complex Views
    4. Composite Views

    Answer: A, B. Single-row functions can be used in Inline as well as Simple views.

    21.Which of the following can contain a group of data?

    1. Composite View
    2. Simple View
    3. Complex View
    4. None of the above

    Answer: C. Complex view can use group function in the query.

    22.What among the following is true about a View?

    1. Sub-queries can be embedded in a CREATE VIEW statement
    2. A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax
    3. You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement
    4. None of the above

    Answer: A. View definition can make use of sub-queries.

    23.Which of the following can create a view even if the base table(s) does not exist?

    1. NOFORCE
    2. FORCE
    3. OR REPLACE
    4. CREATE VIEW

    Answer: B. Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn”t created.

    24.Which of the following commands ensures that no DML operations can be performed on a view?

    1. NOFORCE
    2. FORCE
    3. WITH READ ONLY
    4. OR REPLACE

    Answer: C. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it”s important that users can only query data, not make any changes to it.

    25.What is true about the NOFORCE option in CREATE VIEW statement?

    1. It creates a view even if the base table(s) does not exist.
    2. It creates a view only if the base table(s) exists.
    3. It is the default while creating a view.
    4. None of the above

    Answer: B, C. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn”t created.

    26.What is true about the OR REPLACE keyword?

    1. Object privileges are lost when a view is created using this keyword
    2. There is no need of re granting the object privileges previously granted on it
    3. Neither of A nor B
    4. None of the above

    Answer: B. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view”s previous version should be replaced with the one defined in the new command.

    27.What is true with respect to accessing the below view? (Assume the table structure given)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    CREATE VIEW salVU100
AS SELECT employee_id  ID_NUMBER, last_name NAME, salary*12 ANNUAL_SAL
FROM employees E
WHERE department_id= 100;
    1. The view has to be accessed by the original column names defined in the base table
    2. The view has to be accessed by the aliases given in the view query
    3. View is a simple view
    4. None of the above

    Answer: B, C. View must refer the column alias if the view definition contains alias for the columns.

    28.What is true with respect to accessing the below view? (Assume the table structure given)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    CREATE VIEW salVU100 (ID_NUMBER, NAME, ANNUAL_SAL)
AS SELECT employee_id , last_name, salary*12
FROM employees E
WHERE department_id= 100;
    1. It is not mandatory that the number of aliases match the no. of expressions in the sub-query
    2. It is mandatory that the no. of aliases listed must match the no. of expressions selected in the sub-query
    3. It is mandatory to give aliases while creating a view
    4. None of the above

    Answer: B. If the alias are specified in the view header, same number of columns must be selected in the SELECT query.

    29. Consider the following statement and the given table structure:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    CREATE OR REPLACE VIEW empVU100
(ID_NUMBER, NAME, ANNUAL_SAL, DEPT_ID)
AS
SELECT employee_id , first_name ||'' ''|| last_name, salary, department_id
FROM employees
WHERE department_id= 100;

    What is true about the column aliases as in the above query?

    1. Column aliases are listed in a random order as the columns in the sub-query
    2. Column aliases are listed in the same order as the columns in the sub-query
    3. Column aliases are mandatory while using the CREATE OR REPLACE keyword
    4. We cannot use concatenation when we use the CREATE OR REPLACE

    Answer: B.

    Consider the following statement and answer the questions 30 to 34 that follow:

    CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;

    30.What can be said about the statement given above?

    1. Alternative names have been given for the view
    2. Giving alternative names is mandatory if any column is derived from a function or an expression
    3. Both A and B
    4. None of the above

    Answer: C. Specifying alias name is good practice to improve the readability of the code and the view queries.

    31.What will happen if the above statement is modified as below?

    CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;
    1. It will be no different than the original statement
    2. It will execute successfully giving the same results but change in alias names.
    3. It will throw an ORA error
    4. None of the above

    Answer: B. The sequence of the column alias not matters much as they don”t carry any behavioral attribute.

    32.Determine the output of the below DELETE statement.

    DELETE FROM dept_sum_vu;
    1. It will delete the view
    2. It will remove all the rows from the view but the structure of the view will remain the same
    3. It will throw an ORA error
    4. None of the above

    Answer: C. The view DEPT_SUM_VU is a complex view. DML operations cannot be performed on a complex view.

    33.Suppose you modify the query given above to the following: 

    CREATE OR REPLACE VIEW dept_sum_vu(name, sal)
AS
SELECT d.dept_name, e.salary
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
Where rownum < 10;

    What will be the impact of the modification?

    1. The view can be updated to update the values in EMPLOYEES and DEPARTMENTS tables
    2. Data in EMPLOYEES and DEPARTMENTS tables cannot be deleted through view
    3. Data in EMPLOYEES and DEPARTMENTS tables can be inserted through view
    4. A column can be added to EMPLOYEES table through the view

    Answer: B. DML operations cannot be performed on complex views. DEPT_SUM_VU is a complex view as it joined multiple tables. DDL operations are not possible on views.

    34.Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

    1. The rows will get removed without any error
    2. Only the first 10 rows will get removed
    3. The rows cannot be deleted.
    4. None of the above

    Answer: C. The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

    35.When can the rows from a view be removed?

    1. Deletion of rows through a view is not possible
    2. It should be a simple view
    3. It should be a complex view
    4. None of the above

    Answer: B. DML operations are possible only on simple views.

    36.When can the data in a view not be modified?

    1. When there are group expressions
    2. When there is a GROUP BY clause
    3. When ROWNUM is used in the view query
    4. All of the above

    Answer: D. UPDATE is not possible on a view containing group functions, pseudocolumns or DISTINCT keyword.

    37. The JOB_HISTORY table is owned by a user “Andy”. Andy grants the SELECT privilege on the JOB_HISTORY table to another user “HR”. Which statement would create a synonym EMP_JOBS so that “HR” can execute the following query successfully?(Assume the structure of tables as given)

    SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
    SELECT * from EMP_JOBS;
    1. Andy issues – 
      CREATE SYNONYM EMP_JOBS for JOB_HISTORY
    2. HR issues – 
      CREATE SYNONYM EMP_JOBS for andy.JOB_HISTORY
    3. HR issues – 
      CREATE PUBLIC SYNONYM EMP_JOBS FOR andy.JOB_HISTORY
    4. None of the above

    Answer: B. Only SYSDBA or a user with DBA privileges can create public synonyms.

    38.Which keyword can assure that the DML operations performed on the view stay in the domain of the view?

    1. OR REPLACE
    2. CREATE
    3. WITH CHECK OPTION
    4. None of the above

    Answer: C. The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don”t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause.

    Consider the following table structure and the given statement and answer the questions 39 and 40 that follow:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    CREATE OR REPLACE VIEW empvu100
AS
SELECT * FROM employees
WHERE department_id= 100
WITH CHECK OPTION CONSTRAINT empvu100_ck;

    39.What will the above statement do?

    1. It will allow the users to perform INSERT or UPDATE on all departments
    2. It will allow the user to perform INSERT or UPDATE any row which has department 100
    3. The user can UPDATE any row in the employees table
    4. The user can INSERT rows without any restriction in the employees table

    Answer: B. The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don”t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause. An ORA error will be thrown if an INSERT or UPDATE will be executed on any row with a department_id other than 100.

    40.Suppose you fire an UPDATE statement as shown below:

    UPDATE empvu100
Set department_id = 200
Where employee_id  = 121;

    What will be the outcome of this statement?

    1. No rows are updated
    2. An ORA error is thrown
    3. Both A and B
    4. None of the above

    Answer: C. If the view with CHECK OPTION is updated and new record”s value violates the scope of the view, ORA exception “ORA-01402: view WITH CHECK OPTION where-clause violation” is raised.

    41.What is true about the WITH CHECK CONSTRAINT?

    1. INSERTs or UPDATEs performed through the view cannot create rows that the view cannot select
    2. Only INSERTs performed through the view cannot create rows that the view cannot select
    3. Only UPDATEs performed through the view cannot create rows that the view cannot select
    4. None of the above

    Answer: A.

    42.How can you prevent DML operations on a View?

    1. By defining a WITH CHECK OPTION constraint
    2. By defining a WITH READ ONLY option
    3. Neither of A nor B
    4. None of the above

    Answer: B. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it”s important that users can only query data, not make any changes to it.

    Consider the table structure and the given statement and answer the questions 43, 44 and 45 that follow:

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    CREATE OR REPLACE empvu100(employee_id , first_name, job)
AS
SELECT employee_id , last_name, job
FROM employees
WHERE department_id = 100
WITH READ ONLY;

    43.What is true about the above statement?

    1. The view will not be created
    2. INSERT operation on this view an will throw an ORA error
    3. On UPDATING the rows for all the employees in department 100, an ORA error will be thrown
    4. None of the above

    Answer: B, C. DML operations are not permitted on view which are created with READ ONLY option.

    44.How many rows can be deleted from the view as shown above?

    1. All rows of the view
    2. All the rows of only the department 100
    3. No rows
    4. None of the above

    Answer: C. DML operations are not permitted on view which are created with READ ONLY option.

    45.Which of the following statements will drop the view created as above?

    1. DROP READ ONLY VIEW empvu100;
    2. DROP NOFORCE empvu100;
    3. DROP VIEW empvu100;
    4. None of the above

    Answer: C. Read only view can be dropped using the DROP VIEW command.

    46.What is true about dropping a View?

    1. The columns in the view from the base tables are also dropped
    2. The definition of the view is removed from the database
    3. Dropping of a view has no effect on the underlying base table
    4. None of the above

    Answer: B, C.

    47.Which of the following privileges should a user have to drop a view?

    1. CREATE ANY VIEW
    2. CREATE VIEW
    3. DROP ANY VIEW
    4. DROP VIEW

    Answer: C.

    48.What is true about sequences?

    1. It generates integers
    2. It is a shareable object
    3. Can be used to create a PRIMARY KEY value
    4. All of the above

    Answer: D. A sequence speeds up the efficiency of accessing sequence values when cached in memory

    49.What is true about a sequence?

    1. It is created when the Oracle Database is installed
    2. It is created by a user who has CREATE SEQUENCE system privilege
    3. It cannot be shared by more than two users
    4. It drops automatically when the database is disconnected.

    Answer: B, C. CREATE SEQUENCE system privilege is required by a user to create a sequence in its own schema which cannot be shared by other users.

    50.What among the following options is true about Sequences?

    1. The integers generated through a sequence for one table cannot be used by other tables
    2. A sequence can only be incremented
    3. A sequence becomes invalid if another sequence generates the same integers
    4. A sequence can be used by many tables and they can be incremented or decremented

    Answer: D.

    Consider the following statement and answer the questions 51 to 59 that follow:

    CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 9999
NOCACHE
NOCYCLE;

    51.What will be the first value generated by this sequence?

    1. 1
    2. 100
    3. 101
    4. 9999

    Answer: C. The START WITH clause establishes the starting value for the sequence. Oracle 11g begins each sequence at 1 unless another value is specified in the START WITH clause.

    52.What can be the last value generated by this sequence?

    1. 0
    2. 100
    3. 101
    4. 9999

    Answer: D. The MINVALUE and MAXVALUE clauses establish a minimum or maximum value for the sequence.

    53.What will be the 2nd value generated by this sequence?

    1. 102
    2. 100
    3. 99
    4. 9999

    Answer: A. The INCREMENT BY clause specifies the interval between two sequential values. If the sequence is incremented by a positive value, the values the sequence generates are in ascending order. However, if a negative value is specified, the values the sequence generates are in descending order. If the INCREMENT BY clause isn”t included when the sequence is created, the default setting is used, which increases the sequence by one for each integer generated.

    54.What will be the next value after the maximum integer 9999 is reached by this sequence?

    1. 101
    2. No value
    3. It will throw an ORA error
    4. None of the above

    Answer: B. The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value.

    55.How many values will Oracle pre allocate in memory based on the sequence given above?

    1. 20
    2. 0
    3. 100
    4. 9999

    Answer: A.

    56.You execute the below query:

    SELECT dept_depid_seq.NEXTVAL from dual;
    Assuming that the last value the sequence generated was 200, what will be the outcome of this query?
    1. 200
    2. 101
    3. 9999
    4. 201

    Answer: D. The NEXTVAL pseudocolumn will generate the next unique integer of the sequence.

    57.You execute the below query:

    SELECT dept_depid_seq.CURRVAL from dual;
    Assuming that the last value the sequence generated was 200, what will be the outcome of this query?
    1. 200
    2. 101
    3. 9999
    4. 201

    Answer: A. The CURRVAL pseudocolumn will generate the current unique integer already generated by the sequence.

    58.Suppose you need to change the start value of this sequence to 1000. Which of the following statements will help?

    1. ALTER dept_deptid_seq
INCREMENT BY 100
START WITH 1000
MAXVALUE 9999
NOCACHE
NOCYCLE;
    2. The sequence has to be dropped and re-created to start the sequence from 1000. 
    3. ALTER SEQUENCE dept_deptid_seq
START WITH 101
    4. ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
CYCLE;

    Answer: B. Starting number of a sequence cannot be modified. Oracle raises the exception “ORA-02283: cannot alter starting sequence number”.

    59.Suppose that the above sequence is altered as below:

    ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 99
NOCACHE
NOCYCLE;

    What will be the outcome of this alteration?

    1. ORA error
    2. The maximum value for the altered sequence will now be 99
    3. Neither of A nor B
    4. None of the above

    Answer: A. The MAXVALUE cannot be less than the START WITH value while altering a sequence.

    60.When can we use the CYCLE option in Sequences?

    1. If we want to purge the old rows faster
    2. If we do not want to use the sequence to generate PRIMARY KEY values
    3. Both A and B
    4. None of the above

    Answer: C. The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value. If the CYCLE option is specified and Oracle 11g reaches the maximum value for an ascending sequence or the minimum value for a descending sequence, the CYCLE option initiates the cycle of numbers again.

    61.What is true about NEXTVAL pseudo column?

    1. It re-generates the CURRVAL of a sequence
    2. It returns the next available sequence value
    3. It can return duplicate values
    4. It generates the same values for different users

    Answer: B. The pseudocolumn NEXTVAL (NEXT VALUE) is used to actually generate the sequence value. In other words, it calls the sequence object and requests the value of the next number in the sequence. After a value is generated, it”s stored in the CURRVAL (CURRENT VALUE) pseudocolumn so that you can reference it again.

    62.What is true about CURRVAL pseudo column?

    1. CURRVAL can be used before NEXTVAL with respect to a sequence
    2. CURRVAL gives the current value of a sequence
    3. CURRVAL can give duplicate values
    4. None of the above

    Answer: B.

    63.When can NEXTVAL and CURRVAL be used?

    1. SET clause of an INSERT statement
    2. VALUES clause of an UPDATE statement
    3. The SELECT list of a SELECT statement that is not part of a sub-query
    4. The SELECT list of an INSERT statement

    Answer: C, D. The sequence can be used in SELECT query, PL/SQL cursor or in IAS (INSERT-AS-SELECT)direct operations.

    64.When can NEXTVAL and CURRVAL not be used?

    1. The SELECT list of a view
    2. The SELECT statement with the DISTINCT keyword
    3. A sub-query in SELECT, DELETE or UPDATE statement
    4. All of the above

    Answer: D.

    Consider the given statement and answer the questions 65 and 66 that follow:

    CREATE TABLE employees
(employee_id  NUMBER(4) DEFAULT emp_empid_seq.CURRVAL,
 department_id NUMBER(4));

    65.What will be the outcome of this statement? (Assume that emp_empid_seq is sequence used to generate employee ID values)

    1. Table will be created
    2. The department_id column will have the values from the sequence generated for the employee ID
    3. The department_id column will have a DEFAULT value
    4. ORA error

    Answer: D. Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

    66.What will be the outcome of this statement if the CURRVAL is replaced with NEXTVAL? (Assume that emp_empid_seq is generated to generate employee ID values)

    1. Table will be created
    2. The department_id column will have the values from the sequence generated for the employee ID
    3. The department_id column will have a DEFAULT value
    4. ORA error

    Answer: D. Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

    Examine the given exhibit giving the structures of the tables Departments and Location. Answer the questions 67 and 68 that follow:

    SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
    SQL> desc locations
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 LOCATION_ID		 NOT NULL NUMBER(4)
 STREET_ADDRESS 		  VARCHAR2(40)
 POSTAL_CODE			  VARCHAR2(12)
 CITY			 NOT NULL VARCHAR2(30)
 STATE_PROVINCE 		  VARCHAR2(25)
 COUNTRY_ID			  CHAR(2)

    67.You need to insert a new department named “HR” in the location ID 1000. Which of the following statements will give you the required results?

    1. INSERT INTO departments (dept_id, dept_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, ''HR'', 1000); 
    2. INSERT INTO departments (dept_id, dept_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, "HR", 1000); 
    3. INSERT INTO departments (dept_id, dept_name, location_id)
VALUES (dept_deptid_seq.CURRVAL, ''HR'', 1000);
    4. None of the above

    Answer: A.The option C will cause a ”Unique constraint violation” as it will try to insert current value of department id which aleady exists in the DEPARTMENTS table.

    68.Suppose you execute the below query before inserting the values as shown in the option A in question 67. What will be the outcome of the query?

    SELECT dept_deptid_seq.CURRVAL FROM DUAL;
    1. ORA error
    2. It will give the current value of the sequence
    3. Neither of A nor B
    4. None of the above

    Answer: B. When a user logs in to Oracle 11g, no value is initially stored in the CURRVAL pseudocolumn; the current value is NULL. After a NEXTVAL call has been issued to generate a sequence value, CURRVAL stores that value until the next value is generated. CURRVAL contains only the last value generated.

    69.How can gaps occur in the values of a sequence?

    1. When a rollback occurs
    2. The system crashes
    3. A sequence is used in another table
    4. All of the above

    Answer: D.

    70.What is true about caching sequence values?

    1. Caching sequence values is not possible in Oracle
    2. The cache is populated when the maximum limit of the sequence is reached
    3. Caching starts the first time when the sequence is referred
    4. None of the above

    Answer: C. If the NOCACHE option is specified when the sequence is created, each number is generated when the request is received. However, if an organization”s transactions require large amounts of sequential numbers throughout a session, the CACHE option can be used to have Oracle 11g generate a set of values ahead of time and store them in the server”s memory. Then, when a user requests a sequence value, the next available value is assigned-without Oracle 11g having to generate the number. On the other hand, if the CACHE option isn”t specified, Oracle 11g assumes a default option of CACHE 20 and stores 20 sequential values in memory automatically for users to access.

    71.The following query for the sequence EMP_EMPID_SEQ is executed after a transaction which inserted five employee details.

    Select emp_empID_seq.CURRVAL from dual;

    Suppose the employee transaction rolled back. What will be the result of the above query?

    1. The sequence value at the starting of employee transaction
    2. NULL
    3. The sequence value at the end of employee transaction
    4. None of the above

    Answer: C. Sequence values are unaffected by commit or rollback. If a transaction which uses sequence generator is rolled back, the sequence values are wasted and cannot be recovered.

    72.Which of the following privileges are required to modify a sequence?

    1. CREATE OR REPLACE privilege
    2. ALTER privilege for the sequence
    3. ALTER TABLE privilege
    4. UPDATE privilege

    Answer: B. To alter a sequence, the sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

    73.What happens when a sequence is altered?

    1. The existing integers already generated by the sequence are altered as well
    2. Only the future integers are affected
    3. The sequence stops caching the future integers
    4. None of the above

    Answer: B. By using the ALTER SEQUENCE command, any changes are applied only to values generated after the modifications are made.

    74.Suppose you need to drop a sequence. Which of the following commands will help?

    1. ALTER SEQUENCE sequence_name START WITH NULL;
    2. DROP sequence_name;
    3. DROP SEQUENCE sequence_name;
    4. None of the above

    Answer: C. The DROP command is used to drop a sequence

    75.Which of the following privileges will allow you to drop a sequence? (Choose the most appropriate answer)

    1. ALTER SEQUENCE
    2. ALTER TABLE
    3. DROP SEQUENCE
    4. DROP ANY SEQUENCE

    Answer: D. To drop a sequence, either the sequence must be in your own schema or you must have the DROP ANY SEQUENCE system privilege.

    76.What is true about Indexes?

    1. Indexes are only manually created
    2. Indexes are only automatically created
    3. Both A and B
    4. None of the above

    Answer: D. Indexes can be created manually as well as automatically following certain actions like creating a primary key or unqiue constraint.

    77.Which of the following is used by an index to locate the data quickly?

    1. ROWNUM
    2. ROWID
    3. Sequence
    4. None of the above

    Answer: B. An Oracle 11g index is a database object that stores a map of column values and the ROWIDs of matching table rows. A ROWID is the physical address of a table row.

    78.What happens when there is no index on a column of a table?

    1. The data is located quickly
    2. There is a full table scan
    3. The table cannot be created
    4. The table cannot be altered

    Answer: B.

    79.What among the following is true about an Index?

    1. Index reduces the disk I/O
    2. Index locates data quickly
    3. Indexes are logically and physically independent of the table that they index
    4. All of the above

    Answer: D.

    80.What will happen if an index is dropped?

    1. The column on which the index is created, is dropped too
    2. The table on which the index is created, is dropped too
    3. Indexes once created cannot be dropped
    4. As Indexes are logically and physically independent objects, they can be dropped without affecting other objects

    Answer: D. Indexes are the objects which are physically stored in schema. Dropping an index doesn”t impacts other objects.

    81.What happens when a table is dropped?

    1. The indexes still remain as they are logically and independent objects
    2. The indexes in the table are also dropped
    3. Neither of A nor B
    4. None of the above

    Answer: B.

    82.How are indexes created automatically?

    1. When we create a table
    2. When a table is altered
    3. When a PRIMARY KEY is defined on a column (or group of columns) of a table
    4. When a UNIQUE KEY constraint is defined in the table definition

    Answer: C, D.

    83.For which of the following objects, a synonym can be created?

    1. Tables and views only
    2. Table, view and sequence
    3. Stored procedure, function, or package
    4. Synonym

    Answer: B, C, D. The schema object for which you are creating the synonym can be of the following types:Table or object table, View or object view, Sequence, Stored procedure, function, or package, Materialized view, Java class schema object, User-defined object type, Synonym

    84. Which of the following can you use to reference a table owned by another user?

    1. INDEX
    2. TABLE
    3. SYNONYMS
    4. SEQUENCES

    Answer: C. A synonym is an alternative name or alias for a database object.

    85.What among of the following is an example of a Non-unique index?

    1. PRIMARY KEY
    2. UNIQUE KEY
    3. FOREIGN KEY
    4. None of the above

    Answer: C.

    86.Which of the following is the main and basic type of an Index?

    1. Bitmap
    2. B-tree
    3. Unique
    4. Non-unique

    Answer: A, B. The B-tree (balanced-tree) index is the most common index used in Oracle. You can create this type of index with a basic CREATE INDEX statement. A bitmap index varies in structure and use from a B-tree index. This index is useful for improving queries on columns that have low selectivity (low cardinality, or a small number of distinct values).

    87.You need to speed up a query by creating an index on the FIRST_NAME of the EMPLOYEES table. Which of the following statements can you use? (Assume the table structure as shown)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    1. CREATE INDEX emp_first_name_idx
ON employees (first_name); 
    2. CREATE INDEX emp_first_name_idx
ON employees first_name; 
    3. ALTER INDEX emp_first_name_idx
ON employees (first_name);
    4. None of the above

    Answer: A.

    88.What does the UNIQUE keyword do while creating indexes?

    1. It specifies that the value of the column(s) upon which the index is created must be unique
    2. You cannot use the UNIQUE keyword when creating indexes
    3. It specifies that the index that is created can be used only by one table
    4. None of the above

    Answer: A. A unique index is typically created automatically when a PRIMARY KEY or UNIQUE constraint is defined on a column. Unique indexes can also be explicitly created by including the UNIQUE keyword in the CREATE INDEX statement.

    89.What will happen when you specify the keyword BITMAP while creating an Index?

    1. It creates the index with a bitmap for each distinct key.
    2. It does not create the index on each row separately
    3. Both A and B
    4. None of the above

    Answer: C.

    90.You have queries written which are expected to retrieve less than 2% to 4% of rows. Which of the following can be applied on the relevant tables to achieve the query performance of such query? (Choose the best answer)

    1. Indexes
    2. UNION set operator
    3. MINUS set operator
    4. None of the above

    Answer: A. Indexes are the best way to achieve query performance. Heavy IO operations can be reduced and simplified using index scans.

    91.In what scenarios can Indexes be useful?

    1. If the table(s) is very large
    2. If a column has less values
    3. If a column contains a large number of NULL values
    4. If a column has a wide range of values

    Answer: C, D.

    92.The table EMPLOYEES is updated frequently. When can Indexes be created on this table? (Choose the most appropriate answer)

    1. Indexes should not be created if a table is updated frequently
    2. Indexes should be created at the time when the table is created
    3. Neither of A nor B
    4. None of the above

    Answer: A. Frequent or bulk DML operations on a table with an index add an overhead of maintaining the index segment, which might affect the DML operation performance.

    93.Consider the following query and answer the following query. Assume that the EMPLOYEE_ID , DEPARTMENT_ID and FIRST_NAME columns of EMPLOYEES table are indexed. (Assume the table structure as given)

    SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
    SELECT first_name, last_name
FROM employees
WHERE comm IS NULL;

    Will the existing indexes help in this case if there are 1 million rows in the table EMPLOYEES?

    1. Yes
    2. No
    3. It might help
    4. None of the above

    Answer: B. Indexes are not used when the query predicates do not contain the columns on which the index is created.

    94.Which of the following will remove an Index?

    1. DELETE FROM index_name; 
    2. DROP INDEX index_name; 
    3. DROP INDEX;
    4. None of the above

    Answer: B. You must have the DROP ANY INDEX privilege to drop an index.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc