I have added 20 beginner-level coding questions, Hope these questions help you guys.
1. Create an Employee table
CREATE TABLE EMPLOYEE
INSERT INTO EMPLOYEE VALUES(1,"RAMU","US")
2. Add a column to a table
ALTER TABLE EMPLOYEE
ADD SALARY INT
3. Delete new column
ALTER TABLE EMPLOYEE
DROP COLUMN SALARY
4. update column using CASE
UPDATE EMPLOYEE SET DESC1 = CASE
WHEN NAME = 'JENNIFER' THEN CONCAT(NAME,'IS BEST')
WHEN NAME = 'MICHAEL' THEN CONCAT(NAME,' ','IS BEST')
WHEN NAME = 'PAT' THEN CONCAT(NAME,'IS BEST')
ELSE CONCAT(NAME,' IS BEST')
END
5. How to find unique employees from the table using window functions
ROW_NUMBER
WITH T1 AS ( SELECT *,ROW_NUMBER() OVER( ORDER BY EMPLOYEE_ID) AS RN
FROM EMPLOYEE)
SELECT * FROM T1 WHERE RN = 1
6. write a code to display the difference between RANK() vs DENSE_RANK()
RANK()
SELECT *,RANK() OVER( ORDER BY EMPLOYEE_ID) AS RN
FROM EMPLOYEE
DENSE_RANK()
FROM EMPLOYEE
SELECT *,DENSE_RANK() OVER( ORDER BY EMPLOYEE_ID) AS RN
FROM EMPLOYEE
7.To insert a new record
INSERT INTO Customers (CustomerName, Address, City, PostalCode,Country)
VALUES('Hekkan Burger','Gateveien 15','Sandnes','4306','Norway')
8. update table columns with the same values
UPDATE CUSTOMERS SET
CITY = 'OSLO',COUNTRY = 'NORWAY'
WHERE CUSTOMERID = 32
9. Delete all the records from the Customers table.
DELETE FROM CUSTOMER
10. Select all records where the second letter of the City is an "a".
SELECT * FROM Customers
WHERE City LIKE '_a%'
11. Select all records where the first letter of the City is an "a" or a "c" or an "s".
SELECT * FROM Customers
WHERE City LIKE '[acs]%'
12. Select all records where the first letter of the City starts with anything from an "a" to an "f".
SELECT * FROM CUSTOMER
WHERE CITY LIKE '[A-F]%'
13.Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".
SELECT * FROM Customers
WHERE City LIKE '[!acf]%'
14.Use the IN operator to select all the records where the Country is either "Norway" or "France".
SELECT * FROM Customers
WHERE Country IN('Norway', 'France')
15.Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
SELECT * FROM Products
WHERE Price
BETWEEN 10 AND 20
16. Difference Between Truncate Vs Drop
The DELETE command in SQL removes one or more rows from a table based on the condition specified in those rows .It is a DML(Data Manipulation Language) command , after making changes to the DELETE command .It deletes rows one at a time and applies criteria to each deletion.It makes a record of each and every transaction in the log file.
SQL's TRUNCATE command is used to purge a table of all of its rows, regardless of whether or not any conditions are met. It is a DDL(Data Definition Language) command. When you use the TRUNCATE command, your modifications are committed for you automatically. It purges all of the information in one go. The only activity that is recorded is the deallocation of the pages on which the data is stored.
17.To Select Top 5 Record
SELECT TOP 5 * FROM RECORD
18.write a code to have a column with a Primary Key
CREATE TABLE EMPLOYEE1 (
ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMPLOYEE_ID int,
NAME VARCHAR(20),
SALARY int
)
19 how to update a particular column
UPDATE EMPLOYEES
SET
ADDRESS = '1300 CARTER ST',
CITY = 'SAN JOSE',
WHERE
EMPLOYEEID = 3
20.How to replace a character in a string
UPDATE EMPLOYEE SET FULLNAME = REPLACE(FULLNAME, ' . ', '')
Comments