SQL Upwork Test

0
736

We don’t guarantee that all provided answers are correct.

Which of the following sql command can be included to sort data?
Answer

  • SORT WITH
  • SORT BY
  • ORDER BY Answer
  • LIST BY

Which of the following commands is used to explain access to data?
Answer

  • EXPLAIN PATH
  • EXPLAIN PLAN
  • CALL
  • EXPLAIN ACCESS

Which of the following clauses can be included in select fetch 10 records only? ( Check all that apply)
Note: There may be more than one right answer.
Answer

  • LIMIT 10
  • TOP 10
  • LIMIT 10 OFFSET 1
  • ONLY 10

Referential Integrity means that among relations
Answers

  • A record cannot be deleted if it has any associated data
  • A foreign key value is a subject of its primary key
  • There are no duplicate values in a primary key
  • The table must be normalized to 2NF

Which of the following command will work as per below statement?
“We need to replace a string named “foo” with “bar” in a column having ID <= 4″
Answers

  • UPDATE tabel_name SET column_name= REPLACE(‘foo’, ‘bar’) where ID <= 4;
  • UPDATE tabel_name SET column_name= REPLACE(column_name, ‘foo’, ‘bar’) where ID <= 4;
  • UPDATE tabel_name SET column_name= REPLACE(column_name, ‘bar’, ‘foo’) where ID <= 4;

Which of the following is the correct syntax for writing sub query?
Answer

  • SELECT sub.* FROM (SELECT * FROM abc where day= ‘Friday’) where sub.resolution = ‘NONE’
  • SELECT * FROM (SELECT * FROM abc where day= ‘Friday’) where sub.resolution = ‘NONE’
  • SELECT sub.* FROM (SELECT * FROM abc where day= ‘Friday’) sub where sub.resolution = ‘NONE’

Is there a difference between AND and & (ampersand) in SQL?
Answer

  • Ampersand doesn’t have support in SQL:2008 standard
  • AND prioriy is higher than &;
  • AND has lower prioriy in comprasion with ampersand
  • There is no difference.

What is difference between Decimal and numeric data types
Answer

  • decimal – type of numerical data with a fixed precision and scale, numeric – type of numerical data without fixed precision and scale;
  • numeric – type of numerical data with a fixed precision and scale, decimal – numeric data type without fixed precision and scale;
  • decimal – numeric data type with a fixed precision and scale, numeric – exact numeric data type with integer precision;
  • numeric – numeric data type with a fixed precision and scale, decimal – exact numeric data type with integer precision;
  • There is no difference

Which one of the following is the correct way to delete a column from a table?
Answer

  • ALTER TABLE tabel_name DELETE COLOUMN column_name
  • UPDATE TABLE tabel_name DROP COLOUMN column_name
  • ALTER TABLE tabel_name DROP COLOUMN column_name
  • ALTER TABLE tabel_name REMOVE COLOUMN column_name

Transacion, if available, wrap __ operations.
Answer

  • DDL
  • DML
  • DCL
  • DDL

Consider two tables A and B having only one column each and having these values:
A = {0, 1, 2, 3, 4, 5}
B = {5, 6, 7, 8, 9, 10}
The number of records in result of UNION operator on these table will be?
Answer

  • 1
  • 11
  • 0
  • 12

Which of the following is not a DDL statement?
Answer

  • CREATE TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • INNER JOIN

Which of the following is true for creating SQL Views? (check any that apply)
There may be more than one right answer.
Answer

  • If you have a complex select with losts of joins, you can implement it in a view and simply call the view without need to consider all these join.
  • Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user’s access to stored data
  • A view can draw data from several different tables and present it as a single table, turning multi-label queries into single-table queries against the view.

To create a table column which should not accept blank values, which of the following can be used?
Answer

  • NULL
  • NOT NULL
  • BLANK
  • EMPTY

Suppose you have the following table with coloumn:
id name work_date salary
1 John 2016-01-04 300
To get an output like ‘1John2016-01-04’ which of the following function can be used
Answer

  • CONCAT
  • COMBINE
  • STRING

Assuming there are multiple rows in the ‘Comission_Agents’ table with entries for commission agents from different deparments, what is the error in the following.

SELECT name, deparment_id, commission_percentage from Comission_Agents where commission_percentage = (SELECT min(commission_percentage) FROM Commission_Agents GROUP BY deparment_id)
Answer

  • You cannot use GROUP BY clause in sub-query
  • An aggregate function cannot be used in sub-query
  • You cannot use ‘=’ operator as the sub query is returning multiple results
  • No error

Which operator allows you to specify multiple values in WHERE clause
Answers

  • LIKE
  • IN
  • ==
  • INSIDE

To speed up search in table we can?
Answer

  • create Index
  • truncate table
  • create views

Which of the following statement is/are true?
Answer

  • Select and create are DDL commands
  • Create and Grant are DCL commands
  • Drop and Insert are TCL commands
  • Alter and Drop are DDL commands

Which of the following operator can be used to check if a column contains NULL value
Answer

  • EXISTS
  • IS NULL
  • =
  • <>
  • NOT

Which of the following can be used to delete all the data from a table?
Answer

  • EMPTY
  • DROP
  • TRUNCATE

What does MID() function do?
Answer

  • Returns the middle character
  • Extra characters from a text field
  • Returns the length of a text field

For a table with the following columns:
Product_id, product_name, supplier_id, price
Which of the followings queries will give you the supplier with the maximum average price of products?
Answer

  • Select supplier_id, avg(price) from Products group by supplier_id having avg(price) in (Select max(avg_price) in (select max (avg_price) from (Select avg(price) as avg_price from products group by supplier_id))
  • Select supplier_id, max(avg(price)) from Products group by supplier_id
  • Select supplier_id, avg(price) from Products group by supplier_id having avg(price) in (Select max(avg_price) from (Select avg(price) as avg_price from products)
  • Select supplier_id, avg(price) from Products group by supplier_id having avg(price) in (Select max(avg_price) as avg_price from products group by supplier_id))

Which of the following commands can use a foreign key constraint? (Check any that apply)
Note: There may be more than one right answer
Answer

  • CREATE
  • ALTER
  • UPDATE
  • ALTER

What keyword is used to filter values obtained by applying aggregate functions in the query results using GROUP by clause?
Answer

  • FILTER;
  • WHERE
  • HAVING
  • None of the above;

For a table of the following data

Name Marks
John 400
Brown 200
Darwin 350
Kamy 250

Which of the following queries will give you names of all students have above average marks?
Answer

  • select * from Student where Marks > (select avg(Marks) from [Student])
  • select name from Student where Marks > avg(Marks)
  • select name from Student where Marks > avg(Marks) GROUP BY marks

Which of the following is true about commit command?
Answer

  • Executing commit without begin transaction will result in a error
  • A commit will end all the transaction that were initiated before it
  • A commit will end transaction only in the current session
  • Commit is opposite of rollback

What will be the output of the following query?
select (round(3.5 mod 4)) from DUAL;
Answer

  • 3
  • 3.5
  • 4
  • 4.5

What is difference between CHAR and VARCHAR data types?
Answer

  • CHAR takes fixed space whereas VARCHAR takes variable space
  • CHAR store data as an array of characters, whereas VARCHAR store data as srings
  • CHAR cannot contain NULL values, whereas VARCHAR can contain NULL values
  • CHAR in MySQL is the same as VARCHAR in Oracle

Which statement INSERT or UPDATES Based on the given condition?
Answer

  • Insert
  • Merge
  • Intersect
  • Select

Which of the following can you do with SQL
Answer

  • Update record in database
  • Create new database
  • Send data from database
  • Insert new records to database

Which Query can be used to copy all records from table1 into table2 provided they have the same columns?
Answers

  • CLONE INTO table2 SELECT * FROM table1
  • INSERT INTO table1 FROM table2
  • INSERT INTO table2 FROM table1
  • INSERT INTO table2 SELECT * FROM table1

Which of the following only works with GROUP BY clause?
Answer

  • WHERE
  • ORDER BY
  • HAVING
  • None of the above

What will the output of following query?
Select substring(“987654321”, INSTR(‘foobar’.’o’),5) from DUAL
Answer

  • 98765
  • 87654
  • 76543
  • 65432

Suppose a table CUSTOMERS has the following records:

1 RAMAN 150000
2 Andrew 200000
3 Christi 240000
4 Ivan 240000
5 John 240000
6 Ann 240000
NULL NULL NULL

Which of the following statements will select the customers with income more than 200000?
Answer

  • SELECT * FROM CUSTOMERS WHERE ID IN (SELECT INCOME FROM CUSTOMERS WHERE income > 200000)
  • SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE income > 200000)
  • SELECT * FROM CUSTOMERS WHERE INCOME IN (SELECT CUSTOMERS from CUSTOMERS WHERE INCOME > 200000)
  • SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE INCOME >= 200000)

Which of the following operator can not used in the ‘WHERE’ clause?
Answer

  • <>
  • =
  • <<
  • >=

Which of the following functions are related to XML?
Answer

  • PATH
  • XML TABLE
  • DEPTH
  • XMLPATH

To create a column in table with fixed length (n) string which of the following data type is recommended?
Answer

  • VARCHAR
  • CHAR
  • FIXED
  • STRING

Consider the following Attendance table, which contains employee attendance record for a company:
EmployeeId(Int), LoginTime (time), logouttime(time)
Which of these SELECT statements will list the employee ID that has logged-in most recently ?
Answer

  • SELECT TOP 1 a.EmployeeID FROM Attendance a WHERE EXISTS (SELECT MAX(b.LoginTime) FROM Attendance b GROUP BY EmployeeID HAVING MAX(b.LoginTime) = a.LoginTime AND a.EmployeeID = b.EmployeeID)
  • SELECT TOP 1 a.EmployeeID FROM Attendance a WHERE a.LoginTime = (SELECT MAX(b.LoginTime) FROM Attendance b)
  • SELECT EmployeeID FROM Attendance GROUP By LoginTime HAVING MAX(LoginTime)
  • None of the above

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.