Monday, March 29, 2010

Which is true?

Which statement about SQL is true?

A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT list.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY
clause is not provided.

Answer:[A]
Highlight to find out the answer.

Database snapshot

Which system database can you create a database snapshot in SQL Server 2005?

A. master
B. model
C. msdb
D. tempdb
E. All system databases
F. None

Answer:[C]
Highlight to find out the answer.

Sunday, March 28, 2010

Modify existing table

Examine the structure of contact table:
CONTACT
(
CONTID NOT NULL NUMBER(3),
NAME VARCHAR2(25),
ADDRESS VARCHAR2(50),
BIRTHDATE DATE
)
Currently the table is empty. You have decided that null values should not be
allowed for the NAME column. Which statement restricts NULL values from being
entered into column?

A. ALTER TABLE CONTACT ADD CONSTRAINT NAME(NOT NULL);
B. ALTER TABLE CONTACT ADD CONSTRAINT NOT NULL (NAME);
C. ALTER TABLE CONTACT MODIFY CONSTRAINT NAME(NOT NULL);
D. ALTER TABLE CONTACT MODIFY(NAME VARCHAR2(25) NOT NULL);

Answer:[D]
Highlight to find out the answer.

Add a foreign key

How would you add a foreign key constraint on the dept_no column in the EMP
table. Referring to the ID column in the DEPT table?

A. Use the ALTER TABLE command with the ADD clause in the DEPT table.
B. Use the ALTER TABLE command with the ADD clause on the EMP table.
C. Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
D. Use the ALTER TABLE command with the MODIFY clause on the EMP table.
E. This task cannot be accomplished.

Answer:[B]
Highlight to find out the answer.

Saturday, March 27, 2010

ROLE

Evaluate the SQL statement.
CREATE ROLE manager;
CREATE ROLE clerk;
CREATE ROLE warehouse;
CREATE USER john IDENTIFIED BY john123;
GRANT warehouse TO clerk;
GRANT clerk TO manager;
GRANT warehouse TO john;
GRANT manager TO john
/
How many roles will user john have access to?

A. 0
B. 1
C. 2
D. 3

Answer:[D]
Highlight to find out the answer.

RTRIM

What will be the output of the following script?
SELECT RTRIM('TestxxXXxxx','x') FROM dual;

A. Test
B. TestxxXX
C. TestXX
D. An error occurs: "The RTRIM function requires 1 argument(s)."

Answer:[B]
Highlight to find out the answer.
Prentice Hall Presents Oracle Software - 4 Cd-Roms

Job quiz

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Saturday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

A. Create a job schedule that runs every Saturday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.
B. Create a job for each task that runs once a day. Use a T-SQL statement to check the date and day of the week. If the day is either a Saturday or the first day of the month, execute the code.
C. Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Saturday or the first day of the month, execute the jobs.
D. Create a job for each task that runs once a week on Saturday. Add a second job schedule that runs the job on the first of the month.

Answer: [A]
Highlight to find out the answer.

Thursday, March 25, 2010

Transaction quiz

What will be the output of the following scripts (SQL Server 2005 or 2008)?
CREATE TABLE #Test(a TINYINT)
GO
BEGIN TRY
BEGIN TRAN OuterTran
BEGIN TRAN InnerTran
INSERT INTO #Test VALUES(1)
COMMIT TRAN InnerTran
INSERT INTO #Test VALUES(256) --Error occur here
COMMIT TRAN OuterTran
END TRY
BEGIN CATCH
ROLLBACK WORK
END CATCH
SELECT * FROM #Test

A. 1
B. Error occurs since there no ROLLBACK WORK command in T-SQL
C. Error occurs in the scripts
D. No record returns

Answer:[D]
Highlight to find out the answer.

Wednesday, March 24, 2010

Get Pligg version

SELECT data
FROM pligg_misc_data
WHERE name = 'pligg_version'

Pligg Comment table

Go to Pligg website to learn more.
CREATE TABLE `pligg_comments` (
`comment_id` int(20) NOT NULL auto_increment,
`comment_randkey` int(11) NOT NULL default '0',
`comment_parent` int(20) default '0',
`comment_link_id` int(20) NOT NULL default '0',
`comment_user_id` int(20) NOT NULL default '0',
`comment_date` datetime NOT NULL,
`comment_karma` smallint(6) NOT NULL default '0',
`comment_content` text collate utf8_unicode_ci,
`comment_votes` int(20) NOT NULL default '0',
`comment_status` enum('discard','moderated','published') character set utf8 NOT NULL default 'published',
PRIMARY KEY (`comment_id`),
UNIQUE KEY `comments_randkey` (`comment_randkey`,`comment_link_id`,`comment_user_id`,`comment_parent`),
KEY `comment_link_id` (`comment_link_id`,`comment_parent`,`comment_date`),
KEY `comment_link_id_2` (`comment_link_id`,`comment_date`),
KEY `comment_date` (`comment_date`),
KEY `comment_parent` (`comment_parent`,`comment_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Pligg Category table

Go to Pligg website to learn more.
CREATE TABLE `pligg_categories` (
`category__auto_id` int(11) NOT NULL auto_increment,
`category_lang` varchar(2) collate utf8_unicode_ci default 'en',
`category_id` int(11) NOT NULL default '0',
`category_parent` int(11) NOT NULL default '0',
`category_name` varchar(64) collate utf8_unicode_ci default NULL,
`category_safe_name` varchar(64) collate utf8_unicode_ci default NULL,
`rgt` int(11) NOT NULL default '0',
`lft` int(11) NOT NULL default '0',
`category_enabled` int(11) NOT NULL default '1',
`category_order` int(11) NOT NULL default '0',
`category_desc` varchar(255) collate utf8_unicode_ci default NULL,
`category_keywords` varchar(255) collate utf8_unicode_ci default NULL,
`category_author_level` enum('normal','admin','god') character set utf8 NOT NULL default 'normal',
`category_author_group` varchar(255) collate utf8_unicode_ci default NULL,
`category_votes` varchar(4) character set utf8 NOT NULL default '',
PRIMARY KEY (`category__auto_id`),
KEY `category_id` (`category_id`),
KEY `category_parent` (`category_parent`),
KEY `category_safe_name` (`category_safe_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Tuesday, March 23, 2010

TABLESAMPLE

Do you know TABLESAMPLE in SQL Server 2005?
Assume that your tblEmployee in SQL Server 2005 table contains more than 10 records.
Which querie(s) below return exact 10 records?
1. SELECT EmployeeID, EmployeeName
FROM tblEmployee TABLESAMPLE (10 ROWS)
2. SELECT TOP 10 EmployeeID, EmployeeName
FROM tblEmployee

A. Query 1
B. Query 2
C. Both queries
D. No query return exact 10 records

Answer:[B]
Highlight to find out the answer.

Cross join

What will be the output of the following scripts?
DECLARE @Test1 TABLE(a INT)
DECLARE @Test2 TABLE(a INT)
INSERT INTO @Test1
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
INSERT INTO @Test2
SELECT a FROM @Test1
SELECT COUNT(*) FROM @Test1, @Test2

A. 5
B. 10
C. 25
D. An error will be occurred.

Answer:[C]
Highlight to find out the answer.

Saturday, March 20, 2010

LOWER, UPPER

You want to display the details of all employees whose first names is Bill. But you are not sure in which case first names are stored. Which statement will list all the employees whose first name is Bill?
A. Select last_name, first_name FROM emp WHERE first_name= 'bill';
B. Select last_name, first_name FROM emp WHERE UPPER (first_name)= 'bill';
C. Select last_name, first_name FROM emp WHERE first_name = UPPER ('bill');
D. Select last_name, first_name FROM emp WHERE LOWER(first_name)= 'bill';

Answer:[D]
Highlight to find out the answer.

NVL

You need to create a report to display the issue date and total items issued of your warehouseissue table. If the item has not been issued your report must display not issued. If the total is not available your report must say not available. In the warehouseissue table the issue date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

A. Select itemid, issuedate "Not issued", total "Not available" FROM warehouseissue;
B. Select itemid, NVL(issuedate, 'Not issued'), NVL (total, "Not available") FROM warehouseissue;
C. Select itemid, NVL(TO_CHAR(issuedate), 'Not issued'), NVL(TO_CHAR(total), 'Not available') FROM warehouseissue;
D. Select itemid, TO_CHAR(issuedate, 'Not issued') TO_CHAR(total, 'Not available') FROM warehouseissue;

Answer:[C]
Highlight to find out the answer.

Monday, March 8, 2010

Love Quotes on Happiness

Where ur pleasure is, there is ur treasure: where ur treasure, there ur heart; where ur heart, there ur happiness. ~Saint Augustine

Friday, March 5, 2010

Get disabled constraints

SELECT OWNER,TABLE_NAME,
CONSTRAINT_NAME,
DECODE(CONSTRAINT_TYPE,
'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') AS CONSTRAINT_TYPE
FROM dba_constraints
WHERE STATUS = 'DISABLED'
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME

Get tables without indexes

SELECT OWNER, TABLE_NAME
FROM (SELECT OWNER, TABLE_NAME
FROM dba_tables
WHERE OWNER NOT IN ('SYS','SYSTEM')
MINUS
SELECT TABLE_OWNER, TABLE_NAME
FROM dba_indexes
WHERE OWNER NOT IN ('SYS','SYSTEM'))
ORDER BY OWNER,TABLE_NAME;

Thursday, March 4, 2010

Bitwise with AND & OR

Do you remember bitwise with AND and OR?
DECLARE @a INT,@b INT,@c INT,@d INT
SELECT @a = 2, @b = 3, @c = 5
SET @d = (@a & @b)| @c
SELECT @d

A. 2
B. 3
C. 5
D. 7
E. 8
F. 10

Answer:[D]
Highlight to find out the answer.

Explanation:
@a = 010
@b = 011
-----------
@a&@b = 010
@c = 101
-----------
@d = 111

Monday, March 1, 2010

CAST with DATETIME2

What will be the output of the following script in SQL Server 2008?
SELECT CAST(0 AS DATETIME2 )

A. 1900-01-01 00:00:00.0000000
B. 0001-01-01 00:00:00.0000000
C. 1753-01-01 00:00:00.0000000
D. An error will be generated: "Explicit conversion from data type int to datetime2 is not allowed."
E. An error will be generated:"Type DATETIME2 is not a defined system type."

Answer:[D]
Highlight to find out the answer.

Oracle Query