41. What is Union, minus and Interact commands?
UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.
42. What is an ALIAS command?
ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
Example-.
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
Here, st refers to alias name for student table and Ex refers to alias name for exam table.
43. What is the difference between TRUNCATE and DROP statements?
TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.
44. What are aggregate and scalar functions?
Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.
Example -.
Aggregate – max(), count – Calculated with respect to numeric.
Scalar – UCASE(), NOW() – Calculated with respect to strings.
45. How can you create an empty table from an existing table?
Example will be -.
Select * into studentcopy from student where 1=2
46. How to fetch common records from two tables?
Common records result set can be achieved by -.
Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam
47. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
To display odd numbers-
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
48. How to select unique records from a table?
Select unique records from a table by using DISTINCT keyword.
Select DISTINCT StudentID, StudentName from Student.
49. What is the command used to fetch first 5 characters of the string?
There are many ways to fetch first 5 characters of the string -.
Select SUBSTRING(StudentName,1,5) as studentname from student
Select RIGHT(Studentname,5) as studentname from student
50. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
- % – Matches zero or more characters.
- _(Underscore) – Matching exactly one character.
Example -.
Select * from Student where studentname like ‘a%’
