SQL queries are often asked in the interviews. Hence I have created a series of questions to practice for. See this for the previous post.
The question for this post:
Consider a table with 3 columns, emp_id, dept, sal.
Find the list of emp_id corresponding to the employees that draw the highest salary in their department.
I have created a list of 60 questions of sql queries that are asked in interviews frequently according to my experience .
1.write a query to get the current date .
All Courses
AI Career Planner
1:1 Coaching/Mentoring
Job Board
SQL
HTML/CSS/JS
Coding
Settings
Logout select sysdate();
2. write a query to retrive the first two characters of the last name from emp table;
select substr(last_name,1,2)from emp;
3.write a query to create a new table whch consists of data and structure copied from the other tble.
create table tmp as select * from emp
4.write a query to create a new table which with only the structure copied from the other table.
create table tmp2 as select * from emp where 1 = 0;
5.write a qurey to fetch first N records
select * from emp order by empno limit 5;
6.write a query to fetch the emp first name and emp last name in a single column "Fullname". the first name and last name should be seperated by space
select concat(first_name,' ',last_name) as 'Fullname' from emp;
7.write a query to fetch detalis of all employees whose emp last name ends with 's'
select * from emp where last_name like '_____s'
8.write a query to fetch detalis of all employees excluding the employees with first name smith and blake from emp
select * from emp where first_name not in ('smith','blake')
9.write a query to fetch the department wise count of employees sorted by departments count in ascending order.
select count(empno) c,job from emp
group by job
order by c;
10.write a query to retrive duplicate records from a table
select * from emp
group by empno
having count(empno)>1
diff between delete and truncate
delete -
1 used to delete a row in a table
can rollback the data after the use of delete statement
its a dml(data manpulation language command`) command
it is slower than the truncate statement
truncate -
truncate is used to delete all the rows in a table
you cannot rollback data in truncate
its a ddl(data defination language command) command
it is faster
Q- WHAT ARE THE DIFFERENT SUBSETS OF SQL
DDL - CONSISTS OF THE COMMANDS THAT CAN BE USED TO DEFINE THE DATABASE SCHEMA
DML- CONSISTS OF COMMANDS THAT DEALS WITH THE MANIPULATION OF DATA PRESENT IN THE DATABASE
DCL - INCLUDES COMMANDS WHICH DEAL WITH THE RIGHTS, PERMISSIONS AND OTHER CONTROLS OF THE DATABASE SYSTEM.
TCL - INCLUDES THE COMMANDS WHICH MAINLY DEALS WITH THE TRANSACTION OF DATABASE.
Q- WHAT DO YOU MEAN BY DBMS ? WHAT ARE ITS DIFFERENT TYPES?
A DATABASE MANAGEMENT SYSTEM (DBMS) IS A SOFTWARE APPLICATION THAT INTERACTS WITH THE USER ,APPLICATIONS AND THE DATABASE ITSELF TO CAPTURE AND ANALYSE DATA.
THE DATA STORED IN THE DATABASE CAN BE MODIFIED RETRIEVED AND DELETED , AND CAN BE OF ANY TYPE LIKE STRINGS, NUMBERS, IMAGES,ETC.
( HIERARCHIAL , NETWORK , RELATIONAL , OBJECT-ORIENTED )
Q- WHAT DO YOU MEAN BY A TABLE AND FIELD IN SQL ?
TABLE - A TABLE REFERS TO A COLLECTION OF DATA IN AN ORGANISED MANNER IN FORM OF ROWS AND COLUMNS.
FIELD - A FIELD REFERS TO THE NUMBER OF COLUMNS IN A TABLE .
Q- WHAT ARE JOINS IN SQL ?
A JOIN CLAUSE IS USED TO COMBINE ROWS FROM TWO OR MORE TABLES , BASED ON A RELATED COLUMN BETWEEN THEM . IT IS USED TO MERGE TWO TABLES OR RETRIEVE DATA FROM THERE.
INNER JOIN , FULL JOIN , RIGHT JOIN , LEFT JOIN
Q- WHAT IS THE DIFF BETWEEN CHAR AND VARCHAR2 DATATYPE IN SQL
CHAR- CHAR IS USED FOR STRINGS OF FIXED LENGTH FOR EXAMPLE CHAR(10) CAN ONLY STORE 10 CHARACTERS AND WILL NOT BE ABLE TO STORE A STRING OF ANY OTHER LENGTH .
VARCHAR2 - VARCHAR2 IS USED FOR CHARACTER STRINGS OF VARIABLE LENGTH FOR EXAMPLE VARCHAR2(10) CAN STORE ANY LENGTH I.E 6,8,2 IN THIS VARIABLE.
Q- WHAT IS THE PRIMARY KEY
A SET OF ATTRIBUTES THAT CAN BE USED TO UNIQUELY IDENTIFY EVERY TUPLE IS A PRIMARY KEY . SO , IF THERE ARE 3-4 CANDIDATE KEYS PRESENT IN A RELATIONSHIP, THEN OUT OF THOSE OE CAN BE CHOSEN AS A PRIMARY KEY
Q- WHAT ARE CONSTRAINTS
CONSTRAINTS ARE USED TO SPECIFY THE LIMIT ON THE DATA TYPE OF THE TABLE OF THE TABLE . IT CAN BE SPECIFIED WHILE CREATING OR ALTERING THE TABLE STATEMENT
NOT NULL - ENSURES THAT A NULL VALUE CANNOT BE STORED IN A COLUMN
UNIQUE - THIS CONSTRAINT MAKES SURE THAT ALL THE VALUES IN A COLUMN ARE DIFFERENT
CHECK - THIS CONSTRAINT ENSURES THAT ALL THE VALUES IN A COLUMN SATISFY A SPECIFIC CONDITION
DEFAULT- THIS CONSTRAINT CONSISTS OF A SET OF DEFAULT VALUES FOR A COLUMN WHEN NO VALUE IS SPECIFIED .
INDEX - THIS CONSTRAINT IS USED TO CREATE AND RETRIEVE DATA FROM THE DATABASE VERY QUICKLY.
Q- WHAT IS THE DIFF BETWEEN SQL AND MYSQL
SQL - SQL IS A STANDARD LANGUAGE WHICH STANDS FOR STRUCTRED QUERY LANGUAGE BASED ON THE ENGLISH LANGUAGE. SQL IS THE CORE OF RELATIONAL DATABASE WHICH IS USED FOR ACCESSING AND MANAGING DATABASE.
MYSQL - MYSQL IS AN OPEN-SOURCE RELATIONAL DATABASE MANAGEMENT SYSTEM THAT WORKS ON MANY PLATFORMS. IT PROVIDES MULTI-USER ACCESS TO SUPPORT MANY STORAGE ENGINES AND IS BACKED BY ORACLE.
Q- WHAT IS A UNIQUE KEY
- UNIQUELY IDENTIFIES A SINGLE ROW IN THE TABLE
MULTIPLE VALUES ALLOWED PER TABLE
NULL VALUES ARE ALLOWED
DUPLICATE VALUES ARE NOT ALLOWED
Q - WHAT IS A FOREIGN KEY
FOREIGN KEY MAINTAINS REFERNTIAL INTEGRITY BY ENFORCING A LINK BETWEEN THE DATA IN TWO TABLES
THE FOREIGN KEY IN THE CHILD TABLE REFERENCES THE PRIMRY KEY IN THE PARENT TABLE
THE FOREIGN KEY CONSTRAINT PREVENTS ACTIONS THAT WOULD DESTROY LINKS BETWEEN THE CHILD AND PARENT TABLES
Q - WHAT DO YOU MEAN BY DATA INTEGRITY
DATA INTEGRITY DEFINES ACCURACY OF DATA ,CONSISTANCY OF DATA,INTEGRITY CONSTRAINTS TO ENFORCE BUSINESS RULES ON DATA.
Q - WHAT IS THE DIFFERENCEBETWEEN THE CLUSTERED AND NON CLUSTERED INDEX IN SQL
CLUSTERED INDEX - CLUSTERED INDEX IS USED FOR EASY RETRIEVAL OF DATA FROM THE DATABASE AND IS FASTER
ALTERES THE WAY RECORDS ARE STORED IN A DATABASE AS IT SORTS OUT ROWS BY THE COLUMN WHICH IS SET TO BE CLUSTERED INDEX.
ONE TABLE CAN HAVE ONLY ONE CLUSTERED INDEX.
NON CLUSTERED INDEX - NON CLUSTERED INDEX IS USED FOR EASY RETRIVAL OF DATA FROM THE DATABASE AND IS SLOWER .
NON CLUSTERED INDEX DOES NOT ALTER THE WAY IT WAS STORED BUT IT CREATES A SEPERATE OBJ WHITHIN A TABLE WHICH POINTS BACK TO THE ORIGINAL TABLE
ROWS AFTER SEARCHING .
ONE TABLE CAN HAVE MANY NON CLUSTERED INDEXES
Q - WRITE A SQL QUERY TO DISPLAY THE CURRENT DATE ?
select sysdate();
Q - WHAT ARE THE DIFFRENT TYPES OF JOINS
INNER JOIN - THIS JOIN RETURNS THOSE RECORDS WHICH HAVE MATCHING VALUES IN BOTH THE TABLES.
FULL JOIN - THIS JOIN RETURNS ALL THOSE RECORDS WHICH EITHER HAVE A MATCH IN THE LEFT OR THE RIGHT TABLE.
LEFT JOIN - THIS JOIN RETURNS RECORDS FROM THE LEFT TABLE,AND ALSO THOSE RECORDS WHICH SATISFY THE CONDITION FROM THE RIGHT TABLE.
RIGHT JOIN - THIS JOIN RETURNS RECORDS FROM THE RIGHT TABLE, AND ALSO THOSE RECORDS WHICH SATISFY THE CONDITION FROM THE LEFT TABLE.
Q - WHAT DO YOU MEAN BY DENORMALIZATION
DENORMALIZATION REFERS TO A TECHNIQUE WHICH IS USED TO ACCESS DATA FROM HIGHER TO LOWER FORMS OF A DATABASE
(INCREASE THR PERFORMANCE OF THE ENTIRE INFRASTRUCTURE AS IT INTRODUCES REDUNDANCY INTO A TABLE)
(ADDS THE REDUNDANT DATA INTO A TABLE BY INCORPORATING DATABASE QUERIES THAT COMBINE DATA FROM VARIOUS TABLES INTO A SINGLE TABLE.)
Q - WHAT ARE THE ENTITIES AND RELATIONSHIPS
ENTITIES - A PERSON,PLACE OR THING IN THE REAL WORLD ABOUT WHICH DATA CAN BE STORED IN A DATABASE .TABLES STORE DATA THAT REPRESENTS ONE TYPE OF ENTITY FOR EG-(A BANK DATABASE HAS A CUSTOMER TABLE TO STORE CUSTOMER INFO . CUSTOMER TABLES STORES THIS INFORMATION AS A SET OF ATTRIBUTES FOR EACH CUSTOMER.
RELATIONSHIPS - RELATION OR LINKS BETWEEN THE ENTITIES THAT HAVE SOMETHING TO DO WITH EACH OTHER. FOR EG - THE CUSTOMER NAME IS RELATED TO THE CUSTOMER ACCOUNT NUMBER AND CONTACT INFORMATION WHICH MIGHT BE IN THE SAME TABLE THERE CAN ALSO BE RELATIONSHIPS BETWEEN SEPARATE TABLES (FOR EG CUSTOMER TO ACCOUNTS)
Q - WHAT IS AN INDEX
INDEX REFERS TO AN PERFORMANCE TUNING METHOD WHICH ALLOWS FATER RETRIEVAL OF RECORDS FROM THE TABLE AND CREATES AN ENTRY FOR EACH VALUES
Q - EXPLAIN THE DIFFERENT TYPES OF INDEXES
UNIQUE INDEX - THIS INDEX DOES NOT ALLOW THE FIELD TO HAVE DUPLICATE VALUES IF THE COLUMN IS UNIQUE INDEXED . IF A PRIMARY KEY IS DEFINED , A UNIQUE INDEX CAN BE APPLIED AUTOMATICALLY
Q- WHAT IS NORMALIZATION AND WHAT ARE ITS ADVANTAGES
NORMALIZATIN IS THE PROCESS OF ORGANIZING DATA TO AVOID DUPLICATION AND REDUNDANCY.
ADVANTAGES
.BETTER DATABASE ORGANIZATION
.MORE TABLES WITH SMALLER ROWS
.EFFICIENT DATA ACCESS
.GREATER FLEXIBILITY FOR QUERIES
.QUICKLY FIND THE INFO
.EASIER TO IMPLEMENT SECURITY
.ALLOWS EASY MODIFICATION
.REDUCTION OF REDUNDANT AND DUPLICATE DATA
.MORE COMPACT DATABASE
.ENSURE CONSISTENT DATA AFTER MODIFICATION
Q - WHAT ARE THE DIFFERENCE BETWEEN THE DROP AND THE TRUNCATE COMMAND
DROP - REMOVES A TABLE AND IT CANNOT BE ROLLED BACK FROM THE DATABASE
TRUNCATE - REMOVES ALL THE ROWS FROM THE TABLE AND CANNOT BE ROLLED BACK INTO THE DATABASE
Q - EXPLAIN DIFFERENT TYPES OF NORMALIZATION
THERE ARE MAINLY FOUR TYPES OF NORMALIZATIONS : 1NF,2NF,3NF, & BCNF
1NF - EACH TABLE CELL SHOULD HAVE A SINGLE VALUE .SO,BASICALLY AL THE RECORDS MUST BE UNIQUE
2NF - DATABASE SHOULD BE 1NF AND SHOULD ALSO HAVE A SINGLE COLUMN PRIMARY KEY
3NF - THE SATABASE SHOULD BE 2NF AND MUST NOT HAVE ANY TRANSITIVE FUNCTIONAL DEPEDENCIES
BCNF -IF YOUR DATABASE IS IN 3RD NORMAL FORM THERE WOULD BE SOME SCENARIOS WHERE ANOMALIES WOULD BE PRESENT , IF YOU HAVE MORE THAN CANDIATE KEY .THEN BCNF COMES INTO ROLE WHERE YOU DIVIDE YOUR TABLES FURTHER SO THAT THERE WOULD BE ONLY ONE CANDIDATE KEY PRESENT
Q - WHAT IS THE ACID PROPERTY IN DATABASE
A- ATOMICITY
C- CONSISTENCY
I- ISOLATION(CONCURRENCY CONTROL)
D- DURABILITY
Q - WHAT DO YOU MEAN BY A TRIGGER IN SQL
TRIGGER IN SQL ARE A SPECIAL TYPE OF STORED PROCEDURES THAT ARE DEFINED TO EXECUTE AUTOMATICALLY IN PLACE OR AFTER DATA MODIFICATIONS.IT ALLOWS YOU TO EXECUTE A BATCH OF CODE WHEN AN INSERT , UPDATE OR ANY OTHER QUERY IS EXECUTED AGAINST A SPECIFIC TABLE.
BEFORE INSERT - ACTIVATES BEFORE THE DATA IS INSERTED INTO THE TABLE
AFTER INSERT - ACTIVATES AFTER THE DATA IS INSERTED INTO THE TABLE
BEFORE UPDATE - ACTIVATES BEFORE THE DATA IN THE TABLE IS UPDATED
AFTER UPDATE - " AFTER " " " " " " "
BEFORE DELETE - ACTIVATES BEFORE DATA IS REMOVED FROM THE TABLE
AFTER DELTE - " AFTER " " " " " "
Q - WHAT ARE THE DIFFERENT TYPES OF OPERATORS AVAILABLE IN SQL
AIRTHMETIC OPERATORS
BITWISE OPERATORS
COMPARISON OPERATORS
COMPOUND OPERATORS
LOGICAL OPERATORS
Q - ARE NULL VALUES SAME AS THAT OF ZERO OR A BLANK SPACE?
A NULL VALUES IS NOT AT ALL SAME AS THAT OF ZERO OR A BLANK SPACE.
NULL VALUES REPRESENTS A VALUE WHICH IS UNAVAILABLE ,UNKNOWN ,ASSIGNED OR NOT APPLICABLE
WHEREAS A ZERO IS A NUMBER AND BLANK SPACE IS A CHARACTER.
Q - WAHT IS THE DIFF BETWEEN CROSS JOIN AND THE NATURAL JOIN
CROSS JOIN - PRODUCES THE CROSS PRODUCT OR CARTESIAN PRODUCT OF TWO TABLES
NATURAL JOIN - BASED ON ALL THE COLUMNS HAVING THE SAME NAME AND DATA TYPES IN BOTH THE TABLES.
Q - WHAT IS A SUBQUERY
A SUBQUERY IS A QUERY INSIDE ANOTHER QUERY WHERE A QUERY IS DEFINED TO RETRIVE DATA OR INFO BACK FROM THE DATABASE\
SUBQUERIES ARE ALWAYS EXECUTED FIRST AND THE RESULT OF THE SUBQUERY IS PASSED ON TO THE MAIN QUERY.
Q - WHAT ARE THE DIFF TYPES OF SUBQUERY
CORREALATED SUBQUERY - THESE ARE QUERIES WHICH SELECT THE DATA FROM A TABLE REFERNCED IN THE OUTER QUERY , IT IS NOT CONSIDERED AS AN INDEPENDENT QUERY AS IT REFERS TO ANOTHER TABLE AND REFERS THE COLUMN IN A TABLE
NON - CORREALATED SUBQUERY - THIS QUERY IS AN INDEPENDENT QUERY WHERE THE MAIN OUTPUT OF SUBQUERY IS SUBSTITUTED IN THE MAIN QUERY.
Q - LIST THE WAYS TO GET THE COUNT OF RECORDS IN A TABLE?
SELECT * FROM TABLE
SELECT COUNT(*) FROM TABLE
SELECT ROWS FROM SYSINDEXES WHERE ID = OBJECT_ID(TABLE1) AND INDID <2
Q - WRITE A SQL QUERY TO FIND THE NAMES OF EMPLOYEES THAT BEGIN WITH 'A'
SELECT ENAME FROM EMP
WHERE ENAME LIKE 'A%';
Q - WRITE A SQL QUERY TO GET THE THIRD HIGHEST SALARY OF AN EMPLOYEE FROM EMPLOYEE_TABLE
SELECT TOP 1 SALARY
FROM (
SELECT TOP3 SALARY
FROM EMPLOYEE_TABLE
ORDER BY SALARY DESC) AS EMP
ORDER BY SALARY ASC;
Q - WHAT IS THE NEED OF GROUP FUNCTIONS IN SQL
GROUP FUNCTIONS WORK ON THE SET OF ROWS AND RETURNS ONE RESULT PER GROUP
AVG,COUNT,MAX,MIN,SUM,VARIANCE
Q- WHAT IS A RELATIONSHIP AND WHAT ARE ITS TYPES .
RELATION OR LINKS ARE BETWEEN ENTITIES THAT HAVE SOMETHING TO DO WITH EACH OTHER.RELATIONSHIPS ARE DEFINED AS THE CONNECTION BETWEEN THE TABLES IN A DATABASE.
ONE TO ONE RELATIONSHIP
ONE TO MANY RELATIONSHIP
MANY TO ONE RELATIONSHIP
SELF REFERENCING RELATIONSHIP
Q - HOW CAN YOU INSERT NULL VALUES IN A COLUMN WHILE INSERTING THE DATA ?
1 - IMPLICITLY BY OMITING COLUMN FROM COLUMN LIST
2- EXPLICITLY BY SPECIFYING NULL KEYWORD IN THE VALUES CLAUSE
Q - WHAT IS THE DIFFERENCE BETWEEN THE 'BETWEEEN ' AND 'IN' OPERATORS
BETWEEN - ITS USED TO DISPLAY ROWS BASED ON A RANGE OF VALUES IN A ROW
IN - USED TO CHECK FOR VALUES CONTAINED IN A SPECIFIC SET OF VALUES .
Q - WHY ARE THE SQL FUNCTIONS USED
1-TO PERFORM CALCULATIONS ON DATA
2-TO CONVERT THE DATA TYPES
3-TO FORMAT DATES AND NUMBERS
4-TO MANIPULATE THE OUTPUT
5-TO MODIFY INDIVIDUAL DATA ITEMS
Q - WHAT IS THE NEED OF THE MERGE STATEMENT
ALLOWS CONDITIONAL UPDATE OR INSERTION OF DATA INTO A TABLE
IT PERFORMS AN UPDATE IF A ROW EXISTS OR AN INSERT IF THE ROW DOES NOT EXIST
Q - WHAT DO YOU MEAN BY RECURSIVE STORED PROCEDURE
RECURSIVE STORED PROCEDURE REFERS TO A STORED PROCEDURE WHICH CALLS BY ITSELF UNTILL IT REACHEAS SOME BOUNDARY CONDITION.
THE RECURSIVE FUNCTION OR PROCEDURE HELPS THE PROGRAMMERS TO USE THE SAME SET OF CODE N NUMBER OF TIMES.
Q - WAHT IS A CLAUSE IN SQL
SQL CLAUSES HELP TO LIMIT THE RESULT SET BY PROVIDING A CONDITION TO THE QUERY A CLAUSE HELPS TO FILTER THE ROWS FROM THE ENTIRE SET OF RECORDS.
Q - DIFF BETWEEN WHERE AND HAVING CLASUE
HAVING- CAN BE USED ONLY WITH SELECT STATEMENT IT IS USUALLY USED IN A GROUP BY CLAUSE.
WHERE -IS APPLIED TO EACH ROW BEFORE THEY ARE A PART OF THE GROUP BY FUNCTION IN A QUERY .
Q - LIST THE WAYS IN WHICH DYNAMIC SQL CAN BE EXECUTED.
WRITING A QUERY WITH PARAMETERS
USING EXEC
USING SP_EXECTESQL
Q - WAHT ARE THE VARIOUS LEVELS OF CONSTRAINTS
CONSTARITS ARE THE REPRESENTAION OF A COLUMN TO ENFORCE DATA ENTITY AND CONSISTENCY
1 COLUMN LEVEL CONSTRAINT
2 TABLE LEVEL CONSTRAINT
Q - HOW CAN YOU FETCH COMMON RECORDS FROM TWO TABLES
BY USING INTERSECT
SELECT C1,C2
FROM TABLE_NAMES
WHERE CONDITON
INTERSECT
SELECT STUDENTID FROM STUDENT INTERSECT SELECT STUDENTID FROM EXAM
SELECT C1,C2
FROM TABLE NAMES
WHERE CONDTION
Q - LIST SOME CASE MANIPULATION FUNCTION IN SQL
LOWER - THIS FUNCTION RETURNS THE STRING IN LOWERCASE. IT TAKES A STRING AS AN ARGUMENT AND RETURNS IT BY CONVERTING IT INTO LOWER CASE
LOWER(string)
UPPER - THIS FUNCTION RETURNS THE STRING IN UPPERCASE. IT TAKES A STRING AS AN ARGUMENT AND RETURNS IT BY CONVERTING IT INTO UPPER CASE
UPPER(string)
INITCAP - THIS FUNCTION RETURNS THE STRING WITH THE FIRST LETTER IN UPPERCASE AND REST OF THE LETTERS IN LOWERCASE
INITCAP(STRING)
Q - WHAT ARE THE DIFF SET OEPRATORS AVAILABLE IN SQL
UNION- COMBINES ROWS FROM BOTH QUERIES
INTERSECT - KEEPS ONLY THOSE ROWS WHICH ARE COMMON IN BOTH THE QUERIES
MINUS - LEFT QUERY - RIGHT QUERY KEEPS ROWS FROM THE LEFT QUERY WHICH ARE NOT INCLUDED IN THE RIGHT QUERY
Q - WAHT IS AN ALAIS COMMAND
ALIAS NAME CAN BE GIVEN TO ANY TABLE OR A COLUMN . THIS ALIAS NAME CAN BE REFERRED IN WHERE CLASUE TO IDENTIFY A PARTICULAT TBALE OR COLUMN
Q - WHAT ARE AGGREGATE AND SCALAR FUNCTIONS ?
AGGREGATE FNC - USED TO EVALUATE MATHEMATICAL CALCULATION AND RETURNS A SINGLE VALUE THESE CALCULATIONS ARE DONE FROM COLUMNS IN A TABLE
SACALAR - RETURN A SINGLE VALUE BASED ON THE INPUT VALUE
Q - HOW CAN YOU FETCH ALTERNATE RECORDS FROM A TABLE
YOU CAN FETCH ALTERNATE RECORDS I.E BOTH ODD AND EVEN ROW NUMBERS
SELECT STUDENTID FROM (SELECT ROWNO,STUDENTID FROM STUDENT ) WHERE MOD (ROWNO,2)=0
SELECT STUDENTID FROM (SELECT ROWNO,STUDENTID FROM STUDENT ) WHERE MOD (ROWNO,2)=1
Q- NAME THE OPERATOR WHICH IS USED IN THE QUERY FOR PATTERN MATCHING
LIKE OPERATOR IS USED FOR PATTERN MATCHING
% - IT MATCHES ZERO OR MORE CHARACTERS
_ - IT MATCHES EXACTLY ONE CHARACTER
Q - HOW CAN YOU SELECT UNIQUE RECORDS FROM A TABLE
YOU CAN SELECT UNIQUE RECORDS FROM A TABLE USING THE DISTINCT KEYWORD
Q - HOW CAN YOU FETCH FIRST 5 CHARACTERS OF THE STRING
SELECT SUBSTRING(STUDENTNAME,1,5) AS STUDENTNAME FROM STUDENT
SELECT RIGHT (STUDENTNAME,5) AS STUDENTNAME FROM STUDENT
Q - DIFF BETWEEN SQL & PLSQL
SQL - LANG THAT ALLOWS YOU TO ISSUE A SINGLE QUERY OR EXECUTE A SIGLE INSERT / DELETE / UPDATE
PLSQL - IS ORACLES 'PROCEDURAL LANG'SQL WHICH ALLOWS YOU TO WRITE A FULL PROGRAM TO ACCOMPOLISH MULTIPLE OPERATIONS SUCH AS
SELECT/INSERT/UPDATE/DELETE
Q - WAHT IS A VIEW
A VIEW IS A VIRTUAL TABLE WHICH CONSISTS OF A SUBSET OF DATA CONTAINED IN A TABLE .SINCE VIEWS ARE NOT PRESENT IT TAKES LESS SPACE TO STORE VIEW CAN HAVE DATA OF ONE OR MORE TABLES COMBINED AND IT DEPENDS ON THE RELATIONSHIP
Q - WHAT ARE VIEWS USED FOR
A VIEW REFERS TO A LOGICAL SNAPSHOT BASED ON A TABLE OR ANOTHER VIEW
1 RESTRICTING ACCESS TO DATA
2 MAKING COMPLEX QUERIES SIMPLE
3 ENSURING DATA INDEPENDENCE
4 PROVIDING DIFFERENT VIEWS OF SAME DATA
Q - WHAT IS A STORED PROCEDURE
A FUNCTION WHICH CONSISTS OF MANY SQL STATEMENTS TO ACCESS THE DATABASE SYSTEM
SEVERAL SQL STATEMENTS ARE CONSOLIDATED INTO A STORED PROCEDURE AND ARE EXECUTED WHENEVER AND WHEREVER REQUIRED
THIS SAVES TIME AND AVOIDS WRITING CODE AGAIN AND AGAIN
Q - LIST SOME ADVANTAGES AND DISADVANTAGES OF STORED PROCEDURES
ADVANTAGES - A STORED PROCEDURE CAN BE USED AS A MODULAR PROGRAMMING WHICH MEANS CREATE ONCE,STORE AND CALL FOR SEVERAL TIMES WHENEVERIT IS REQUIRED THIS SUPPORTS FASTER EXECUTION IT ALSO REDUCES NETWORK TRAFFIC AND PROVIDES BETTER SECURITY TO THE DATA
DISADVANTAGES -THE ONLY DISADVANTAGE OF STORED PROCEDURE IS THAT IT CAN BE EXECUTED ONLY IN THE DATABASE AND UTILIZES MORE MEMORY IN THE DATABASE SERVER.
Q - LIST ALL TYPES OF USER DEFINED FUNCTIONS
SCALAR FUNC
MULTI STATEMENT FUNC
INLINE TABLE VALUED FUNC
Q - WHAT DO YOU MEAN BY COLLATION
COLLATION IS DEFINED AS A SET OF RULES THAT DETERMINES HOW DATA CAN BE STORTED AS WELL AS COMPARED
Q - WHAT ARE THE DIFF TYPES OF COLLATION SENSITIVITY
CASE SENSITIVITY
KANA SENSITIVITY
WIDTH SENSITIVITY
ACCENT SENSITIVITY
Q - WHAT ARE LOCAL AND GLOBAL VARIABLES
LV- THESE VARIABLES CAN BE USED OR EXIST ONLY INSIDE THE FUNCTION THESE VARIABLES ARE NOT USED OR REFERRED BY ANY OTHER FUNC
GV- THESE VARIABLES ARE THE VARIABLES WHICH CAN BE ACCESSED THROUGHOUT THE PROGRAM . GLOBAL VARIABLES CANNOT BE CREATED WHENEVER THAT FUNCTION IS CALLED
Q - WHAT IS AUTO INCREMENT IN SOL
ALLOWS THE USER TO CREATE A UNIQUE NUMBER TO GET GENERATED WHENEVER A NEW RECORD IS INSERTED INTO THE TABLE
THIS KEYWORD IS USUALLY REQUIRED WHENEVER THE PRIMARY KEY IS USED
Q - WHAT IS A DATAWHEREHOUSE
DATAWHREHOUSE REFERS TO A CENTRAL REPOSITORY OF DATA WHERE THE DATA IS ASSEMBLED FROM MULTIPLE SOURCES OF INFORMATION
WAREHOUSEDATA ALSO HAVE A SUBSET OF DATA CALLED DATA MARTS
Q - WHAT ARE THE DIFFERENT AUTHENTICATION MODES IN SQL SERVER HOW CAN IT BE CHANGED
START>PROGRAMS>MICROSOFT SQL SERVER
CLICK SQL ENTERPRISE MNAGAER TO RUN SQL ENTERPRISE MANAGER FROM THE MICROSOFT SERVER PROGRAM GROUP
THEN SELECT THE SERVER FROM TOOLS MENU
SELECT SQL SERVER CONFIG PROPERTIES AND CHOOSE THE SECURITY PAGE
Q - WHAT ARE STUFF AND REPLACE PUNCTIONS
STUFF- THIS FUNC IS USED TO OVERWRITE EXISTING CHARACTER OR INSERTS A STRING INTO ANOTHER STRING
REPLACE - THIS FUNC IS USED TO REPLACE THE EXISTING CHARACTERS OF ALL THE OCCURENCIES