Elementry MSSQL Commands - Lab 1

1-      Create Table EmployeeInfo

CREATE TABLE EmployeeInfo
(
Empid INT IDENTITY(1001,1) PRIMARY KEY CLUSTERED,
 Empname VARCHAR (20),
 SalaryInLaC DECIMAL (10,2),
  Location VARCHAR (20)
  )


INSERT INTO EmployeeInfo VALUES('AB1001',15.2,'HYD')
INSERT INTO EmployeeInfo VALUES('AB1002',10.5,'PUNE')
INSERT INTO EmployeeInfo VALUES('AB1003',5.2,'BANG')
INSERT INTO EmployeeInfo VALUES('AB1004',20,'DELHI')
INSERT INTO EmployeeInfo VALUES('AB1005',15.2,'HYD')
INSERT INTO EmployeeInfo VALUES('AB1006',10.5,'PUNE')
INSERT INTO EmployeeInfo VALUES('AB1007',5.2,'BANG')
---------------------------------------------------Upto 2000
INSERT INTO EmployeeInfo VALUES('AB2000',10.5,'DELHI')




2. Find out how many of the employees are there in Hyd and Pune
SELECT COUNT(1) FROM EMPLOYEEINFO
WHERE LOCATION LIKE 'HYD'
OR LOCATION LIKE 'PUNE'

3. Find out how many of the employees have the package >10

SELECT COUNT(1) FROM EmployeeInfo
WHERE SALARYINLAC >= 10

4. Find out how many of the employees have the package > 10 and <15
SELECT COUNT(1) FROM EmployeeInfo
WHERE SALARYINLAC >= 10
AND SalaryInLac <=15

5. Create a query to get following data. This should be only one query - hint group by
Location NumberofEmp
Hyd        x
Pune       y
Chennai    z

SELECT Location, COUNT(1) As NumberofEmp
FROM EmployeeInfo
GROUP BY LOCATION



6. Create the clustered index on the empid column and tell me the size of table and index.

sp_spaceused EmployeeInfo






7. Create a nonclustered index on empname column and again tell me the size of table and index.

ALTER TABLE EmployeeInfo
add
CONSTRAINT Emp_id UNIQUE NONCLUSTERED (EMPNAME)





8. Now Pour this all the data into a test table.

SELECT * INTO TestTable
FROM EmployeeInfo

9. Change the column datatype from varchar to char(1800)

ALTER TABLE EmployeeInfo
ALTER COLUMN Empname CHAR(100)





--Then I droped the constraint and then alter the column then again recreated the constraint-

ALTER TABLE EmployeeInfo
--DROP CONSTRAINT EMP_ID
ALTER COLUMN Empname VARCHAR(50)

Again verify the size of table and index-






10. Delete the rows only for Hyd location. Now get the data from back table after you deleted.
delete from employeeInfo
where location like 'HYD'
or location like 'pune'

Now get the data from back table after you deleted.-

INSERT INTO EMPLOYEEINFO (Empid, Empname, SalaryInLaC, Location)
  SELECT DISTINCT Empid, Empname, SalaryInLaC, Location FROM TestTable

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'EMPLOYEEINFO' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I TRIED-
SET IDENTITY_INSERT EMPLOYEEINFO ON
go
INSERT INTO EMPLOYEEINFO (Empid, Empname, SalaryInLaC, Location)
SELECT DISTINCT Empid, Empname, SalaryInLaC, Location FROM TestTable

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Employee__AF2EBFA115502E78'. Cannot insert duplicate key in object 'dbo.EmployeeInfo'.
The statement has been terminated.


I did below changes and got my task doneJ

SET IDENTITY_INSERT EMPLOYEEINFO ON
GO
INSERT INTO EMPLOYEEINFO (Empid, Empname, SalaryInLaC, Location)
  SELECT DISTINCT Empid, Empname, SalaryInLaC, Location
     FROM TestTable
        WHERE location = 'HYD'
        OR location    = 'pune'
Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life


Comments

Post a Comment

Popular posts from this blog

MSSQL Interview Question Clustering- Part2

Will Indian Rupee Cross 55?? Q: What will happen if you try to start the SQL service on the passive node? Ans: This will start because error log, mdf and ldf files are possessed by active node. Q: How exactly the SQL service starts on any node? Ans: Below is the list of all the steps in error log at the of SQL Service starting on one node, all the logs are self-explanatory- 1-SQLServerAgent service successfully stopped. 2-[sqsrvres] OnlineThread: asked to terminate while waiting for QP. 3-17148 :SQL Server is terminating due to 'stop' request from Service Control Manager. 4-17104 :Server Process ID is 9344. 5-This instance of SQL Server last reported using a process id of 6804 at 1/23/2011 10:39:34 AM (local) 1/23/2011 6:39:34 PM (UTC). 6-SQL Server is starting at priority class 'normal'(16 CPUs detected). 7-SQL Server configured for thread mode processing. 8-17125 :Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Block
Read more

Error 1067: The cluster service is stopped on passive node of my two node sql cluster. It throws following error when I try to start this:

This is found that the cluster service in one of the passive stops. This throws following error when I try to start this. Error 1067: The process terminated unexpectedly I verified the password under which this cluster service is running. This account is perfect. So there should be some other problem. Let's go ahead and see the problem in the cluster.log file. Info from %WINDIR%\Cluster\cluster.log is as follows: WARN [EVT] EvtBroadcaster: EvPropEvents for node 2 failed. status 1727 WARN [NM] RpcExtErrorInfo: Error info not found. Error from clusterlog Error: Failed to read  (sector 12), error 170. and Failed to write  (sector 12), error 170. 1. started from cmd. net start clussvc 2. The Windows Firewall/Internet Connection Sharing service should neither be enabled nor set to start automatically. FmGetQuorumResource failed, error 170. Failed to read. Failed to form cluster, status 5086. Follwoing was the problem: The Admin NIC threw an error. This caused the netowrk c
Read more

Handle Sexual Harassment

Image
Sexual harassment at work place is prevalent. It can be termed as bullying, unwelcome sexual advances and asking sexual favors at workplace. Harassment can happen to anyone and probability of recurrence increases if not tackled professionally. This is often observed that we don't talk enough on this topic. Some EEOC stats as per  link 79% of victims are women, 21% are men. 51% are harassed by a supervisor. Business, trade, banking, and finance are the biggest industries where sexual harassment occurs. 12% received threats of termination if they did not comply with their requests.  How to handle the sexual harassment- This needs to be documented. Victim should send out a mail to the offender informing that she/he is shocked with his/her actions on date at particular venue. I discussed this situaltion with my relatives and they asked me to inform you about my feelings. The victim has to be very professional. Victim needs to make sure that he seek for the read rece
Read more