Computer SQL

DIFFERENCE BETWEEN % AND _

% is a wildcard character used with LIKE and it is used for substituting multiple characters while matching the pattern. Matching text can be of any length _ (underscore) is also a wildcard character used with LIKE but it substitute only single character at given position while matching the pattern. Length will be fixed.

Observe the given Table TEACHER and give the output of question (i) and (ii) TEACHER_CODE TEACHER_NAME DOJ T001 ANAND 2001-01-30 T002 AMIT 2007-09-05 T003 ANKIT 2007-09-20 T004 BALBIR 2010-02-15 T005 JASBIR 2011-01-20 T006 KULBIR 2008-07-11 (i) SELECT TEACHER_NAME,DOJ FROM TEACHER WHERE TEACHER_NAME LIKE „%I%‟ (ii) SELECT * FROM TEACHER WHERE DOJ LIKE „%-09-%‟;

(i) TEACHER_NAME DOJ ------------------------------------------------------- AMIT 2007-09-05 ANKIT 2007-09-20 BALBIR 2010-02-15 JASBIR 2011-01-20 KULBIR 2008-07-11 (ii) TEACHER_CODE TEACHER_NAME DOJ ---------------------------------------------------------------------- T002 AMIT 2007-09-05 T003 ANKIT 2007-09-20

If Table Sales contains 5 records and Raj executed the following queries; find out the output of both the query. (i) Select 100+200 from dual; (ii) Select 100+200 from Sales;

(i) 300 (ii) 300 300 300 300 300

What will be output of following Mysql Queries - (i) Select Round(55.698,2) (ii) Select mid(„examination‟,4,4) (iii) Select Round(4562.778,-2) (iv) Select length(trim(„ exam „))

(i) 55.70 (ii) mina (iii) 4600 (iv) 4

(i) Sanjay was deleting the record of empno=1234, but at the time of execution of command he forgot to add condition empno=1234, what will be the effect of delete command in this case? (ii) Sameer is executing the query to fetch the records of employee who are getting salary between 4000 to 8000, he executed the query as - Select * from employee where salary between 4000 to 8000; But he is not getting the correct output, Rewrite the correct query.

(i) If where clause is missing with DELETE then it will delete all the record of table. (ii) Select * from employee where salary between 40000 and 80000

Which command is used to add new record in table?

INSERT INTO

Which clause is used to search for NULL values in any column?

IS NULL

What is the difference between Equi-Join and Natural Join?

In Equi join we compare value of any column from two tables and it will return matching rows. In Equi-join common column appears twice in output because we fetch using (*) not by specifying column name. for e.g. In Equi-join it is not mandatory to have same name for column to compare of both table In natural join also the matching rows will return. In natural join column will appear only once in output. Then name of column must be same in both table if we are performing natural join using the clause NATURAL JOIN.

OUTPUT Select right(„mysql application‟,3);

Ion IP ONLY

Which clause is used for pattern matching? What are the 2 main characters used for matching the pattern?

LIKE % (percent) and _ (underscore)

What is the minimum number of column required in MySQL to create table?

ONE (1)

Which clause is used to see the output of query in ascending or descending order?

ORDER BY

A table Employee contains 5 Rows and 4 Columns and another table PROJECT contains 5 Rows and 3 Columns. How many rows and columns will be there if we obtain Cartesian product of these two tables?

Rows = 5 x 5 = 25 Columns = 4 + 3 = 7

Raj is a database programmer, He has to write the query from EMPLOYEE table to search for the employee whose name begins from letter „R‟, for this he has written the query as: SELECT * FROM EMPLOYEE WHERE NAME=‟R%‟; But the query is not producing the correct output, help Raj and correct the query so that he gets the desired output.

SELECT * FROM EMPLOYEE WHERE NAME LIKE ‟R%‟;

Raj is a database programmer, He has to write the query from EMPLOYEE table to search for the employee who are not getting any commission, for this he has written the query as: SELECT * FROM EMPLOYEE WHERE commission=null; But the query is not producing the correct output, help Raj and correct the query so that he gets the desired output.

SELECT * FROM EMPLOYEE WHERE commission IS null;

Raj is a database programmer, has to write the query from EMPLOYEE table to search for the employee who are working in „Sales‟ or „IT‟ department, for this he has written the query as: SELECT * FROM EMPLOYEE WHERE department=‟Sales‟ or „IT‟; But the query is not producing the correct output, help Raj and correct the query so that he gets the desired output.

SELECT * FROM EMPLOYEE WHERE department=‟Sales‟ or department=„IT‟; OR SELECT * FROM EMPLOYEE WHERE department IN ('Sales','IT')

Ranjeet created a table named student, He wants to see those students whose name ending with p. He wrote a query- SELECT * FROM student WHERE name="p%"; But the query is not producing the desired output, Help Ranjeet to run the query by removing the errors from the query and rewriting it.

SELECT * FROM student WHERE name LIE "p%";

Write MYSQL command to see the list of tables in current database

Show tables

Suppose a table BOOK contain columns (BNO, BNAME, AUTHOR, PUBLISHER), Raj is assigned a task to see the list of publishers, when he executed the query as: SELECT PUBLISHER FROM BOOK; He noticed that the same publisher name is repeated in query output. What could be possible solution to get publisher name uniquely? Rewrite the following query to fetch unique publisher names from table.

Solution is to use DISTINCT clause. Correct Query : SELECT DISTINCT PUBLISHER FROM BOOK;

What is the full form of SQL?

Structured Query Language

Which command is used to change the existing information of table?

UPDATE

OUTPUT Select round(59999.99,-2);

60000

OUTPUT Select round(7756.452,1);

7756.5

OUTPUT Select instr(„mysql application‟,‟p‟);

8

HOTS Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X = 7 A. 127 B. 255 C. 129 D. 257

A. 127

DIFFERENCE BETWEEN HAVING AND WHERE

HAVING - this clause is used with GROUP BY to filter the group of records. We can use aggregate functions with HAVING. WHERE - this clause is used to apply condition on all the rows of table. We cannot use aggregate functions with WHERE.

Which command is used to add new column in existing table?

ALTER TABLE

Sunil decides to delete a PhoneNo column from a MySQL Table (student) after insert the data into the table. Write the command to delete that particular column in student table.

ALTER TABLE student drop PhoneNo

What is the Difference between ALTER Table command and UPDATE command?

ALTER is DDL command and is used for modifying the schema of table like adding new column, modifying column definition, dropping column. UPDATE is DML command and is used for modifying the existing data of table like changing the mobile number, changing the salary etc.

Which SQL function is used to get the average value of any column?

AVG()

The following query is producing an error. Identify the error and also write the correct query. SELECT * FROM EMP ORDER BY NAME WHERE SALARY>=5000;

As per MySQL, ORDER BY must be the last clause in SQL QUERY, and in this query ORDER BY is used before WHERE which is wrong, the correct query will be: SELECT * FROM EMP WHERE SALARY>=5000 ORDER BY NAME;

What is the difference between COUNT() and COUNT(*) function

COUNT() function will count number of values in any column excluding the NULLs COUNT(*) will count number of rows in query output including NULLs

What are DDL and DML? Give one command of each.

DDL stands for Data Definition Language. DDL commands are used to manipulate the database objects like database, table, views etc. In simple words DDL commands are used to create table, changing the structure of table or dropping the table. Example: CREATE, ALTER & DROP DML stands for Data Manipulation Language. DML commands are used to manipulate the information stored in a table. Like adding new records, changing existing records or deleting the records. Example: INSERT, UPDATE & DELETE

Which option of ORDER BY clause is used to arrange the output in descending order?

DESC

Which command is used to see information like name of columns, data type, size etc. ?

DESCRIBE OR DESC

Which clause is used to eliminate the duplicate rows from output?

DISTINCT

Which command is used to remove the table from database?

DROP TABLE

Query to delete all record of table without deleting the table: a. DELETE TABLE TABLE_NAME b. DELETE FROM TABLE_NAME c. DROP TABLE TABLE_NAME d. DELETE TABLE FROM TABLE_NAME

b. DELETE FROM TABLE_NAME

Identify the wrong statement about UPDATE command a. If WHERE clause is missing all the record in table will be updated b. Only one record can be updated at a time using WHERE clause c. Multiple records can be updated at a time using WHERE clause d. None of the above

b. Only one record can be updated at a time using WHERE clause

Identify the correct statement(s) to drop a column from table a. DELETE COLUMN COLUMN_NAME b. DROP COLUMN COLUMN_NAME c. ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME d. ALTER TABLE TABLE_NAME DROP COLUMN_NAME

c. ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME d. ALTER TABLE TABLE_NAME DROP COLUMN_NAME

Write a MySQL query to create the given table (MEMBER) Column name Datatype Size ID Char 6 Name Varchar 30 Fee Int 10 DOJ Date

create table member(id char(6),name varchar(30),fee int(10),doj date)

OUTPUT - Select Substring('mysql application',3,3)

sql