The GRANT command enables privileges on the database objects and the REVOKE command removes them. They are DCL commands
GRANT CREATE ANY TABLE TO username
GRANT sysdba TO username
GRANT DROP ANY TABLE TO username
REVOKE CREATE TABLE FROM username
The BCP (Bulk Copy) is a utility or a tool that exports/imports data from a table into a file and vice versa
Rights are given to a user, usually by the DBA, to perform a particular action on the database schema objects like creating tablespaces.
The following are examples of system privileges that can be granted to users:
1. CREATE TABLE allows a grantee to create tables in the grantee's schema.
2. CREATE USER allows a grantee to create users in the database.
3. CREATE SESSION allows a grantee to connect to an Oracle database to create a user session.
When two transactions are granted read access to the same data, they are given a shared lock. This enables reading the same data, and data is not updated until the shared lock is released.
There are several good things about them. The very first thing is they consume almost no space which makes them good enough to be considered in every situation. At the same time, the users are able to consider views for simply retrieving the outcomes that belong to queries that are complicated in nature.
The ORDER BY Clause is used to sort records in a table.
It is an extension of SQL(Structured Query Language) developed by Sybase and used by Microsoft.
SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’
FROM employee
WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’
SELECT DATEADD(mm, 2, getdate())
YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.
we can store Videos inside SQL Server by using FILESTREAM data type, which was introduced in SQL Server 2008.
YES, we can store this sort of data using a blob datatype
OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data into the DB.
A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.
Forward cursors support fetching of rows from start to end from a result set. You cannot go to the previous row in the result set.
Cursors operate on individual rows, and in the case of a set, it works on a resultant set of data, which could be a table/view or a join of both. The resultant set is an output of a SQL query.
sp_depends system stored procedure or query the says depends on system table to return a list of objects that a user-defined function depends upon
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so1.name = '<>'
The clauses of the subselect are processed in the following sequence (DB2):
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
7. FETCH FIRST clause
The AS keyword is optional when specifying a column alias.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
CHAR is used to store fixed-length character strings, and VARCHAR2 is used to store variable-length character strings.
For example, suppose you store the string ‘Database’ in a CHAR(20) field and a VARCHAR2(20) field.
The CHAR field will use 22 bytes (2 bytes for leading length).
The VARCHAR2 field will use 10 bytes only (8 for the string, 2 bytes for leading length).
DML includes the most common SQL statements to store, modify, delete, and retrieve data. They are SELECT, UPDATE, INSERT, and DELETE
No, the same is not possible
This is one of the tricky SQL Interview Questions. Interviewer may ask you this question in another way as what are the advantages of Union All over Union.
Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different.
Union: It omits duplicate records and returns only distinct result set of two or more select statements.
Union All: It returns all the rows including duplicates in the result set of different select statements.
256, check SQL Server Limit
CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed.
The largest number that can be represented in a single byte is 11111111 or 255. The number of possible values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).
The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,
OSQL is a command-line tool that is used to execute the query and display the result the same as a query analyzer but everything is in command prompt.
Commands that are used for the purpose of managing the data present in the database:-
1. Update
2. Insert
This integration provides a wider range of development with the help of CLR for database servers because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net.
The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is used in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors
User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case, you could create a user-defined data type called Flight_num_type of varchar(8) and use it across all your tables.
You can answer these questions based on the commands you have used in your past if you having a bit of experience in SQL. Else, the following commands are there which are widely adopted and are very useful.
Data Definition Language
Transaction Control Language
Data Query Language
Data Manipulation Language
Data Control Language
The key which is created on multiple columns in a table is generally considered as the Composite primary key. However, it is not always necessary that all of them have the same meaning.
EXEC sp_executesql,
EXECUTE()
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.
YES, SQL Server support this
SQL Server can be lined to any server provided it has an OLE-DB provider from Microsoft to allow a link.
For E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as a linked server to the SQL Server group.
While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR.QUERTY >> RESULT TO >> Result to FILE
Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.
Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.
Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;
When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.
However, when GROUP BY is used:
The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tieâ€; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks†to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).
For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.
In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan†(located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.
An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer join is the one that is useful for returning the value of rows and tables that generally include the records that must be the same in all the tables.
It is basically a program that is considered when it comes to maintaining, creating, deploying, controlling as well as monitoring the use of a database. It can also be considered as a file manager which is good enough to be trusted for managing the data kept in a database than a file system.
SQL Server is Microsoft's relational database management system (RDBMS). End-user cannot interact directly with the database server. If we want to interact with the SQL database server then we have to interact with SQL.