SQL Server Is Microsoft's Relational Database Management System (RDBMS). It Is A Full-featured Database Primarily Designed To Compete Against Competitors Oracle Database (DB) And MySQL.
1.What is DBMS?
A Database Management System (DBMS) is a program that controls creation, maintenance and use of adatabase. DBMS can be termed as File Manager that manages data in a database rather than saving it in filesystems.
2.What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS store the data into the collection oftables, which is related by common fields between the columns of the table. It also provides relationaloperators to manipulate the data stored into the tables.
3.What is SQL?
SQL stands for Structured Query Language , and it is used to communicate with the Database. This is astandard language used to perform tasks such as retrieval, updating, insertion and deletion of data from adatabase. Standard SQL Commands are Select.
4.What is a Database?
Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data.This is also known as structured form of data which can be accessed in many ways.
5.What are tables and Fields?
A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized asvertical, and Rows are horizontal. A table has specified number of column called fields but can have anynumber of rows which is called record.
Example:.
Table: Employee.
Field: ID, Emp Name, Date of Birth.
Data: 201456, David, 11/15/1960.
6.What is a primary key?
A primary key is a combination of fields which uniquely specify a row. This is a special kind of uniquekey, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.
7.What is a unique key?
A Unique key constraint uniquely identified each record in the database. This provides uniqueness for thecolumn or set of columns. A Primary key constraint has automatic unique constraint defined on it. But not,in the case of Unique Key. There can be many unique constraint defined per table, but only one Primarykey constraint defined per table.
8.What is a foreign key?
A foreign key is one table which can be related to the primary key of another table. Relationship needs tobe created between two tables by referencing foreign key with the primary key of another table.
9.What is a join?
This is a keyword used to query data from more tables based on the relationship between the fields of thetables. Keys play a major role when JOINs are used.
10.What are the types of join and explain each?
There are various types of join which can be used to retrieve data and it depends on the relationshipbetween tables.
Inner join.
Inner join return rows when there is at least one match of rows between the tables.
Right Join.
Right join return rows which are common between the tables and al rows of Right hand side table. Simply,it returns al the rows from the right hand side table even though there are no matches in the left hand sidetable.
Left Join.
Left join return rows which are common between the tables and al rows of Left hand side table. Simply, itreturns al the rows from Left hand side table even though there are no matches in the Right hand side table.
Full Join.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all therows from the left hand side table and al the rows from the right hand side table.
11.What is normalization?
Normalization is the process of minimizing redundancy and dependency by organizing fields and table ofa database. The main aim of Normalization is to add, delete or modify field that can be made in a singletable.
12.What is Denormalization.
DeNormalization is a technique used to access the data from higher to lower normal forms of database. Itis also process of introducing redundancy into a table by incorporating data from the related tables.
13.What are all the different normalizations?
The normal forms can be divided into 5 forms, and they are explained below -.
First Normal Form (1NF):.
This should remove al the duplicate columns from the table. Creation of tables for the related data andidentification of unique columns.
Second Normal Form (2NF):.
Meeting all requirements of the first normal form. Placing the subsets of data in separate tables andCreation of relationships between the tables using primary keys.
Third Normal Form (3NF):.
This should meet al requirements of 2NF. Removing the columns which are not dependent on primary keyconstraints.
Fourth Normal Form (3NF):.
Meeting all the requirements of third normal form and it should not have multi valued dependencies.
14.What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Views are not virtuallypresent, and it takes less space to store. View can have data of one or more tables combined, and it isdepending on the relationship.
15.What is an Index?
An index is performance tuning method of al owing faster retrieval of records from the table. An indexcreates an entry for each value and it will be faster to retrieve data.
16.What are all the different types of indexes?
There are three types of indexes -.
Unique Index.
This indexing does not allow the field to have duplicate values if the column is unique indexed. Uniqueindex can be applied automatically when primary key is defined.
Clustered Index.
This type of index reorders the physical order of the table and search based on the key values. Each tablecan have only one clustered index.
NonClustered Index.
NonClustered Index does not alter the physical order of the table and maintains logical order of data.Each table can have 999 nonclustered indexes.
17.What is a Cursor?
A database Cursor is a control which enables traversal over the rows or records in the table. This can beviewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval,addition and removal of database records.
18.What is a relationship and what are they?
Database Relationship is defined as the connection between the tables in a database. There are various databasing relationships, and they are as follows:.
One to One Relationship, One to Many Relationship, Many to One Relationship and Self-ReferencingRelationship.
19.What is a query?
A DB query is a code writ en in order to get the information back from the database. Query can bedesigned in such a way that it matched with our expectation of the result set. Simply, a question to theDatabase.
20.What is sub query?
A sub query is a query within another query. The outer query is called as main query, and inner query iscalled sub query. Sub Query is always executed first, and the result of sub query is passed on to the mainquery.
21.What are the types of sub query?
There are two types of sub query – Correlated and Non-Correlated. A correlated sub query cannot beconsidered as independent query, but it can refer the column in a table listed in the FROM the list of themain query. A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.
22.What is a stored procedure?
Stored Procedure is a function consists of many SQL statement to access the database system. SeveralSQL statements are consolidated into a stored procedure and execute them whenever and whereverrequired.
23.What is a trigger?
A DB trigger is a code or programs that automatically execute with response to some event on a table orview in a database. Mainly, trigger helps to maintain the integrity of the database.
Example: When a new student is added to the student database, new records should be created in therelated tables like Exam, Score and Attendance tables.
24.What is the difference between DELETE and TRUNCATE commands?
DELETE command is used to remove rows from the table, and WHERE clause can be used for conditionalset of parameters. Commit and Roll back can be performed after delete statement. TRUNCATE removes alrows from the table. Truncate operation cannot be rolled back.
25.What are local and global variables and their differences?
Local variables are the variables which can be used or exist inside the function. They are not known to theother functions and those variables cannot be referred or used. Variables can be created whenever thatfunction is called.
Global variables are the variables which can be used or exist throughout the program. Same variabledeclared in global cannot be used in functions. Global variables cannot be created whenever that functionis called.
26.What is a constraint?
Constraint can be used to specify the limit on the data type of table. Constraint can be specified whilecreating or altering the table statement. Sample of constraint are.
NOT NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY and FOREIGN KEY.
27.What is data Integrity?
Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrityconstraints to enforce business rules on the data when it is entered into the application or database.
28.What is Auto Increment?
Auto increment keyword allows the user to create a unique number to be generated when a new record isinserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword canbe used in SQL SERVER. Mostly this keyword can be used whenever PRIMARY KEY is used.
29.What is the difference between Cluster and Non-Cluster Index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records arestored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within thetable. It point back to the original table rows after searching.
30.What is Data warehouse?
Data warehouse is a central repository of data from multiple sources of information. Those data areconsolidated, transformed and made available for the mining and online processing. Warehouse data havea subset of data called Data Marts.
31.What is Self-Join?
Self-join is set to be query used to compare to itself. This is used to compare values in a column with othervalues in the same column in the same table. ALIAS ES can be used for the same table comparison.
32.What is Cross-Join?
Cross join defines as Cartesian product where number of rows in the first table multiplied by number ofrows in the second table. If suppose, WHERE clause is used in cross join then the query will work like anINNER JOIN.
33.What is user defined functions?
User defined functions are the functions writ en to use that logic whenever required. It is not necessary towrite the same logic several times. Instead, function can be called or executed whenever needed.
34.What are all different types of collation sensitivity?
Following are different types of collation sensitivity -.
Case Sensitivity – A and a and B and b.
Accent Sensitivity.
Kana Sensitivity – Japanese Kana characters.
Width Sensitivity – Single byte character and double byte character.
35.Advantages and Disadvantages of Stored Procedure?
Stored procedure can be used as a modular programming – means create once, store and cal for severaltimes whenever required. This supports faster execution instead of executing multiple queries. Thisreduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the databaseserver.
36.What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which can be used fordata entry and easy retrieval processing of data. This processing makes like easier on simplicity andefficiency. It is faster, more accurate results and expenses with respect to OTLP.
37.What is CLAUSE?
SQL clause is defined to limit the result set by providing condition to the query. This usual y filters somerows from the whole set of records.
Example – Query that has WHERE condition
Query that has HAVING condition.
38.What is recursive stored procedure?
A stored procedure which cal s by itself until it reaches some boundary condition. This recursive functionor procedure helps programmers to use the same set of code any number of times.
39.What is Union, minus and Interact commands?
UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from thetables.
MINUS operator is used to return rows from the first query but not from the second query. Matchingrecords 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.
40.What is an ALIAS command?
ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause toidentify the table or column.
Example-.
1 Select st.StudentID, Ex. Result from student st, Exam as Ex where
Here, st refers to alias name for student table and Ex refers to alias name for exam table.
41.What is the difference between TRUNCATE and DROP statements?
TRUNCATE removes al the rows from the table, and it cannot be rolled back. DROP command removes atable from the database and operation cannot be rolled back.
42.What are aggregate and scalar functions?
Aggregate functions are used to evaluate mathematical calculation and return single values. This can becalculated 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.
43.How can you create an empty table from an existing table?
Example will be -.
1 Select * into student copy from student where 1=2;
Here, we are copying student table to another table with the same structure with no rows copied.
44.How to fetch common records from two tables?
Common records result set can be achieved by -.
1 Select studentID from student. <strong>INTERSECT </strong>
45.How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
1 Select studentId from (Select row no, studentId fromstudent)
To display odd numbers-.
1 Select studentId from (Select row no, studentId from student)
from (Select row no, studentId from student) where mod(rowno,2)=1.[/sql]
46.How to select unique records from a table?
Select unique records from a table by using DISTINCT keyword.
1 Select DISTINCT StudentID,Student Name from Student.
47.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 -.
1 Select SUBSTRING(StudentName,1,5) as student name from student;
1 Select RIGHT(Studentname,5) as student name from student;
48.Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
1. Matches zero or more characters.
2._(Underscore) – Matching exactly one character.
1 Select * from Student where student name like ‘a%’ ;
1 Select * from Student where student name like ‘ami_’;
49.What is MySQL?
MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquiredby Oracle)
50.What are the technical features of MySQL?
MySQL database software is a client or server system which includes
Multithreaded SQL server supporting various client programs and libraries
Different backend
Wide range of application programming interfaces and
Administrative tools.
51.Why MySQL is used
MySQL database server is reliable, fast and very easy to use. This software can be downloaded asfreeware and can be downloaded from the internet.
52.What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.
• BLOB or TEXT fields are not al owed
• Only comparison operators can be used =, <,>, = >,=<
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL
53.What is the default port for MySQL Server?
The default port for MySQL server is 3306.
54.What are the advantages of MySQL when compared with Oracle?
55.Differentiate between FLOAT and DOUBLE?
Following are differences for FLOAT and DOUBLE:
• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
56.Differentiate CHAR_LENGTH and LENGTH?
CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latincharacters but they are different for Unicode and other encodings.
57.How to represent ENUMs and SETs internally?
ENUMs and SETs are used to represent powers of two because of storage optimizations.
58.What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values and that can be used during tablecreation.
59.Define REGEXP?
REGEXP is a pattern match in which matches pattern anywhere in the search value.
60.Difference between CHAR and VARCHAR?
Following are the differences between CHAR and VARCHAR:
CHAR and VARCHAR types differ in storage and retrieval
CHAR column length is fixed to the length that is declared while creating table. The length value rangesfrom 1 and 255
When CHAR values are stored then they are right padded using spaces to specific length.
61.Give string types available for column?
The string types are:
62.How to get current MySQL version?
1SELECT VERSION ()?
is used to get the current version of MySQL.
63.What storage engines are used in MySQL?
Storage engines are called table types and data is stored in files using various techniques.
Technique involves:
64.What are the drivers in MySQL?
Following are the drivers available in MySQL:
65.What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?
TIMESTAMP column is updated with Zero when the table is created. UPDATECURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is achange in other fields of the table.
66.What is the difference between primary key and candidate key?
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.Primary Key is also a candidate key. By common convention, candidate key can be designated as primaryand which can be used for any foreign key references.
67.How do you login to MySql using Unix shell?
We can login through this command
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
68.What does myisamchk do?
It compress the MyISAM tables, which reduces their disk or memory usage.
69.How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable cal end
max_heap_table_size.
70.What is the difference between MyISAM Static and MyISAM Dynamic?
In MyISAM static al the fields will have fixed width. The Dynamic MyISAM table will have fields likeTEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easierto restore in case of corruption.
71.What are federated tables?
Federated tables which allow access to the tables located on other databases on other servers.
72.What, if a table has one column defined as TIMESTAMP?
Timestamp field gets the current timestamp whenever the row gets altered.
73.What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
It stops incrementing. Any further inserts are going to produce an error, since the key has been usedalready.
74.How can we find out which auto increment was assigned on Last insert?
LAST_INSERT_IDCareerarm.com return the last value assigned by Auto_increment and it is not requiredto specify the table name
75.How can you see all indexes defined for a table?
Indexes are defined for the table by:SHOW INDEX FROM <tablename>?
76.What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
77.How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.
78.What are the column comparisons operators?
The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons inSELECT statements.
79.How can we get the number of rows affected by query?
Number of rows can be obtained by
1 SELECT COUNT (user_id) FROM users?
80.Is Mysql query is case sensitive?
No.
1) SELECT VERSION(), CURRENT_DATE?
2 )Select version(), current_date?
3 )select version(), current DATE?
All these examples are same. It is not case sensitive.
81.What is the difference between the LIKE and REGEXP operators?
LIKE and REGEXP operators are used to express with ^ and %.
1) SELECT * FROM employee WHERE emp_name REGEXP "^b"?
2) SELECT * FROM employee WHERE emp_name LIKE "%b"?
82.What is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –
They all differ only in the maximum length of the values they can hold.
83.What are the column comparisons operators?
84.How can we get the number of rows affected by query?
85.Is Mysql query is case sensitive?
3 )select version(), current_DATE?
A TEXT is a case-insensitive BLOB. The four TEXT types
TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and comparison is performedincase-sensitive for BLOB values and case-insensitive for TEXT values.
86.What is the difference between mysql_fetch_array and mysql_fetch_object?
Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object - Returns a result row as object from database.
87.How can we run batch mode in mysql?
Following commands are used to run in batch mode:
1) mysql?
2) mysql mysql.out.
88.Where MyISAM table will be stored and also give their formats of storage?
Each MyISAM table is stored on disk in three formats:
89.What are the different tables present in MySQL?
Total 5 types of tables are present:
MyISAM is the default storage engine as of MySQL
90.What is ISAM?
ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to store and retrievedata on secondary storage systems like tapes.
91.What is InnoDB?
lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
92.How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on al columns and it wil be combined with ORDER BY clause.
1 SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a?
93.How to enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and aprefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is astring.
94.How to display top 50 rows?
In MySql, top 50 rows are displayed by using this following query:
1 ) SELECT * FROM
2 ) LIMIT 0,50?
95.How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any standard table.
96.What is the different between NOW() and CURRENT_DATE()?
NOW () command is used to show current year,month,date with hours, minutes and seconds.CURRENT_DATE() shows current year, month and date only.
97.What are the objects can be created using CREATE statement?
Following objects are created using CREATE statement:
98.How many TRIGGERS are allowed in MySql table?
SIX triggers are al owed in MySql table. They are as follows:
99.What are the nonstandard string types?
Following are Non-Standard string types:
100.What are all the Common SQL Function?
CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combinetwo or more fields into one single field.FORMAT(X, D) – Formats the number X to D significant digits.
CURRDATE(), CURRTIME() – Returns the current date or time.
NOW() – Returns the current date and time as one value.
MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.
HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculateage
SUBTIMES(A, B) – Determines the difference between two times.
FROMDAYS(INT) – Converts an integer number of days into a date value.
101.Explain Access Control Lists.
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basisfor MySQL server’s security model and it helps in troubleshooting problems like users not being able toconnect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate orrun a command, MySQL checks the authentication information and permissions against the ACLs, in apredetermined order.