-
Delete Duplicate Records
How to delete duplicate records?
-
Name of the class of SQL?
-
a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
b. SELECT * FROM Persons SORT REVERSE 'FirstName'
c. SELECT * FROM Persons ORDER BY -'FirstName'
d. SELECT * FROM Persons ORDER BY FirstName DESC
SELECT * FROM Persons ORDER BY DESC FirstName
3)What is the correct SQL syntax for selecting all the columns where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
a. SELECT * FROM Persons WHERE LastName > 'Hansen', LastName < 'Pettersen'
b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons
c. SELECT * FROM customers WHERE LastName > 'Hansen' AND LastName > 'Pettersen'
d. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
4)Consider the following queries:
1. select * from employee where department LIKE "[^F-M]%";
2. select * from employee where department = "[^F-M]%";
Select the correct option:
a. Query 2 will return an error
b. Both the queries will return the same set of records
c. Query 2 is perfectly correct
d.Query 2 would return one record less than Query 1
5)Which of the following is not a global variable?
a. @@colcount
b. @@error
c. @@rowcount
d. @@version
e. All are valid global variables
Consider the following two tables:
1. customers( customer_id, customer_name)
2. branch ( branch_id, branch_name )
6)What will be the output if the following query is executed:
Select * branch_name from customers,branch
a. It will return the fields customer_id, customer_name, branch_name
b. It will return the fields customer_id, customer_name, branch_id, branch_name
c. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
d. It will return an empty set since the two tables do not have any common field name
e. It will return an error since * is used alone for one table only
7)Which of the following is not a valid Numeric datatypes in SQL Server?
a. INT
b. SMALLINT
c. TINYINT
d. BIGINT
e. MONEY
8)Which of the following datatypes is not supported by SQL-Server?
a. Character
b. Binary
c. Logical
d. Date
e. Numeric
f. All are supported
9)Which of the following are false for batches (batch commands)?
a. Statements in a batch are parsed, compiled and executed as a group
b. None of the statements in the batch is executed if there are any syntax errors in the batch
c. None of the statements in the batch is executed if there are any parsing errors in the batch
d. None of the statements in the batch is executed if there are any fatal errors
in the batch
10) Select the correct option:
a. Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
b. Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer
11) How can you view the structure of a table named "myTable" in SQL Server?
a. desc myTable
b. desc table myTable
c. sp_columns myTable
d. None of the above
e. Using either option a or c
12) Which of the following is not a valid binary datatype in SQL Server?
a. BINARY
b. VARBINARY
c. BIT
d. IMAGE
e. TESTAMP
13) Which of the following is false with regards to sp_help?
a. When a procedure name is passed to sp_help, it shows the parameters
b. When a table name is passed to sp_help, it shows the structure of the table
c. When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
d. All of the above are true
14)Which of the following are false for batches (batch commands)?
a. Statements in a batch are parsed, compiled and executed as a group
b. None of the statements in the batch is executed if there are any syntax errors in the batch
c. None of the statements in the batch is executed if there are any parsing errors in the batch
d. None of the statements in the batch is executed if there are any fatal errors
in the batch
">1)What is the maximum value that can be stored for a datetime field? a. Dec 31, 9999 b. Jun 6, 2079 c. Jan 1, 2753 d. Jan 1, 2100 2)What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"? a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC b. SELECT * FROM Persons SORT REVERSE 'FirstName' c. SELECT * FROM Persons ORDER BY -'FirstName' d. SELECT * FROM Persons ORDER BY FirstName DESC SELECT * FROM Persons ORDER BY DESC FirstName 3)What is the correct SQL syntax for selecting all the columns where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"? a. SELECT * FROM Persons WHERE LastName > 'Hansen', LastName < 'Pettersen' b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons c. SELECT * FROM customers WHERE LastName > 'Hansen' AND LastName > 'Pettersen' d. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' 4)Consider the following queries:1. select * from employee where department LIKE "[^F-M]%";2. select * from employee where department = "[^F-M]%";Select the correct option: a. Query 2 will return an error b. Both the queries will return the same set of records c. Query 2 is perfectly correct d.Query 2 would return one record less than Query 1 5)Which of the following is not a global variable? a. @@colcount b. @@error c. @@rowcount d. @@version e. All are valid global variables Consider the following two tables:1. customers( customer_id, customer_name)2. branch ( branch_id, branch_name )6)What will be the output if the following query is executed: Select * branch_name from customers,branch a. It will return the fields customer_id, customer_name, branch_name b. It will return the fields customer_id, customer_name, branch_id, branch_name c. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name d. It will return an empty set since the two tables do not have any common field name e. It will return an error since * is used alone for one table only 7)Which of the following is not a valid Numeric datatypes in SQL Server? a. INT b. SMALLINT c. TINYINT d. BIGINT e. MONEY 8)Which of the following datatypes is not supported by SQL-Server? a. Character b. Binary c. Logical d. Date e. Numeric f. All are supported 9)Which of the following are false for batches (batch commands)? a. Statements in a batch are parsed, compiled and executed as a group b. None of the statements in the batch is executed if there are any syntax errors in the batch c. None of the statements in the batch is executed if there are any parsing errors in the batch d. None of the statements in the batch is executed if there are any fatal errorsin the batch 10) Select the correct option: a. Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer b. Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer 11) How can you view the structure of a table named "myTable" in SQL Server?a. desc myTable b. desc table myTable c. sp_columns myTable d. None of the above e. Using either option a or c12) Which of the following is not a valid binary datatype in SQL Server?a. BINARY b. VARBINARY c. BIT d. IMAGEe. TESTAMP13) Which of the following is false with regards to sp_help?a. When a procedure name is passed to sp_help, it shows the parameters b. When a table name is passed to sp_help, it shows the structure of the tablec. When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database d. All of the above are true14)Which of the following are false for batches (batch commands)?a. Statements in a batch are parsed, compiled and executed as a group b. None of the statements in the batch is executed if there are any syntax errors in the batch c. None of the statements in the batch is executed if there are any parsing errors in the batchd. None of the statements in the batch is executed if there are any fatal errorsin the batch
psingla
- Mar 16th, 2010
1. a.Dec 31, 9999 2. a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC suggested: SELECT <column_names> FROM Persons WHERE FirstName ORDER BY FirstName DESC 3. d. SELECT * FROM Pe...
Rsubscribe
- Oct 27th, 2009
Following answers are for SQL Server (few verified on SQL 2005)1. ... (a)2. ... (d)3. ... (d)4. ... (a)5. ... (e) In SQL @@Variable = is a global variable @Variable = local 6. ... (c) It returns ...
-
How to delete records from two tables. There is relationship between two tables.
It's working in MySQL but not in SQL-Server.
Santhoshkandula
- May 18th, 2011
For deleting the records from the relational tables there are 4 options available. These options can be used when you create the tables then you have to use these options and create tables and ...
caveatashish
- Dec 14th, 2007
Use a procedure which will delete record from child table first and then it will delete the same record from parent tableSame thing can be achieved using cascade delete and cascade update, this feature was not there in sql server 7.0
-
What is a Co-related query in databases?
sakthi.jaganathan
- Aug 25th, 2008
CO-RELATED Query: It used to find the exits or doesnot exits value. It has two select statement, the outer select statement will execute first and then the inner select statement execute.There is no need of relation between the two select statement.
srinu.tenali
- Dec 14th, 2007
the sub query is executed more than one time i.e,the inner query depends on outer query output is called co-related query.
-
I have three task in my SSIS package,1. Script Task2. Execute Process Task3. FTP Task I want to enable disable Execute Process Task and FTP Task on runtime using script task, How to do that?Do we have any property of dts using which we can access property of other task iin script task like Dts.Connection("Connectionname") for Connection Controls?
shreeprakashr
- Dec 11th, 2008
Hi, In this case, We can disable the script task at the run time but we cannot disable succeeding tasks based on the variable. I tried and got to know this. Please correct me if i am wrong.RegardsShree
gnanagopal
- Jun 19th, 2008
Hi Dhara,1. create a variable2. assign value for variable in the script task.3. Use expression to enable or disable the taks based on the variable values.let me know if you have any more details.cheers,gnanam.
-
Indexing on frequently updated table
I was asked a question in an interview, where I was given a situation which is:
I have registered to an online gaming website. It displays my rank on the basis of my score when I play a game.
Database table has 2 columns: 1. UserName 2. Score.
Since the login page was loading very slowly so the programmers created an non-clustered index on UserName and now the welcome page problem... -
How will backup database in access?
cybersavvy
- Feb 5th, 2009
To take backup of the Access database, follow the steps:Alt+F --> Manage --> Backup Database -->Specify the path for the backup
Pradip Manna
- Dec 26th, 2006
Backup data from MS Sql Server to Access Database following stapes have to do:-1. Create DSN on SQL server (check Sql server Database name,server name,user name,password)2. open MS access database go to importselect ODBC (DSN name)
-
How we can protect SQL Server Database?Tell me about Process?
psingla
- Mar 8th, 2010
Solution1:Create a replica of a database on a different machine.Update the replica using log shipping functionality of sqlserver.Advantage:Less space is usedDisadvantage:Complex Solution2:Take the complete backups of the databases.Advantage:SimpleDisadvantage:More space is used
pratap das
- Nov 23rd, 2006
this is also worhs mentioning database backup & recovery
-
Assume that I have created a employees table which has 10 records. How to create a stored procedure to export all the records into the log file.
dheeruchakri
- Oct 10th, 2007
Create Procedure TestExportasBeginxp_cmdshell 'bcp "SELECT * from YourDatabase.dbo.YourTable" QueryOut d:Temptest.xls -S alexissqlsrv01 -U dpi -P Value*() -c -T'EndAll the Best.- Dheeraj.
Dheeraj Chakravarthy
- Oct 10th, 2007
Create Procedure TestExportasBeginxp_cmdshell 'bcp "SELECT * from YourDatabase.dbo.YourTable" QueryOut d:Temptest.xls -S alexissqlsrv01 -U dpi -P Value*() -c -T'EndAll the Best.- Dheeraj.
-
What is the Disadvantage of StoredProcedure?
anjan_maity
- Aug 7th, 2008
When a procedure is created a execution plan is created, proc is executed according to that execution plan. When we modify the proc it also follow this execution plan.When index is rebuild, new index ...
Guest
- Jan 10th, 2007
It is possible that the statistics have changed since a stored procedure was compiled and thus may be executing a less the optimal executionplan, thus it may run slower.
-
What are the advantages of sql server 2005 over sql server 2000/ sql server 7.0 ?
gvs
- Jan 3rd, 2008
SQL Server 2005 has reduced application downtime, increased scalability and performance, and tight yet flexible security controls. SQL Server 2005 makes it simpler and easier to deploy, manage, and op...
chandrasekhar
- May 17th, 2007
Sql server 2005 is exclusively designed for dot net developers. what ever the function or procedure that we write in dot net environment those function or procedure that we direcly exucute in sql server environment. this is the new feature in sql 2005.
-
Suppose you create a students record with 5 marks, total and average.How to filter the top 3 students which are getting high average.
-
How to copy one table data into another table directly?
sakthi.jaganathan
- Aug 25th, 2008
We can copy the Table structure also from one table to another tableExample: CREATE table emp1 as &nbs...
srivani123
- Nov 15th, 2007
select into statement will work heresyntax: select column1,column2,....into table2from table1
-
To display list of tables in database
How to display list of tables from a particular database in MS-SQLHow to display description of that particular table?
cybersavvy
- Feb 19th, 2009
To Display list of tables in the database:SELECT * FROM INFROMATION_SCHEMA.TABLES WHERE TYPE='basetable'To describe the structure of a particular tableSP_HELP table_name;
-
Unique Key in SQL Server
How To Define Unique Key in SQL Server
cybersavvy
- Feb 19th, 2009
CREATE TABLE product(pid INT PRIMARY KEY,pname VARCHAR(10),invoiceid INT UNIQUE)
sivasangari_2006
- Jan 30th, 2009
Create table (field1 datatype contraint constraint-name constraint_type,fields2,field3.........)(or)alter table add constraint constraint constraint_name constraint_type(fieldname)Eg:create table emp(eno int constraint uni1 unique,ename varchar(20)
-
Differences between sqlserver2000 and 20005
What are the differences between sqlserver 2000 and sql server 2005
deepintl
- Apr 1st, 2009
1)-In SQL SERVER 2000 there where maximum 16 instances, but in 2005 you can have up to 50 instances.2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.3)-...
khwaja MOhammad Sohail
- Jul 14th, 2008
There are Many Differences like 1.Replication Management: Replication monitor is way usless when compared to the 2000 interface (lots and lots of screens and can not even handle pull subscriptions ent...
-
Stored Procedure Performance Tuning
How can we improve stored procedure performance? Explain the performance tuning techniques.
hiteshvyas
- Apr 21st, 2010
Use the following steps:1) Dont use temporary tables instead use CTE or table Variable2) Use Inner Join instead of Sub Queries.3) Check indexs in tables used4) Instead of Select * from use select colu...
bonagirdipti
- Feb 17th, 2010
To expect improvemnt in stored procedure performance, pl keep following points in mind :-1. Please don't use many subqueries.2. Don't use cursor, instead of it, use temp. table in the looping....
-
Insert Multiple Rows
How to insert multiple rows in a single insert query?
-
Clustered & Non Clustered Indexes
What is difference between clustered & non clustered Indexes?
itmasterw
- Dec 28th, 2010
In general you put a clustered index on the key columns that you will be querying on and you can put a non clustered index on other key columns in the select statement. But you need to really look at ...
psingla
- Nov 8th, 2010
The index in the sql server are in the form of btree having root and leaf nodes.The root node contains the column key value contained in the index and the leaf node contains the table data or pointer ...
SQL Server Interview Questions
Ans