Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Problem 16. Create SoftUni Database
- CREATE DATABASE SoftUni
- USE SoftUni
- CREATE TABLE Towns (
- Id INT PRIMARY KEY IDENTITY,
- [Name] NVARCHAR(30) NOT NULL
- )
- CREATE TABLE Addresses (
- Id INT PRIMARY KEY IDENTITY,
- AddressText NVARCHAR(30) NOT NULL,
- TownId INT FOREIGN KEY REFERENCES Towns(Id)
- )
- CREATE TABLE Departments (
- Id INT PRIMARY KEY IDENTITY,
- [Name] NVARCHAR(30) NOT NULL
- )
- CREATE TABLE Employees (
- Id INT PRIMARY KEY IDENTITY,
- FirstName NVARCHAR(30) NOT NULL,
- MiddleName NVARCHAR(30),
- LastName NVARCHAR(30) NOT NULL,
- JobTitle NVARCHAR(30) NOT NULL,
- DepartmentId INT FOREIGN KEY REFERENCES Departments(Id),
- HireDate DATE NOT NULL,
- Salary DECIMAL(10, 2) NOT NULL,
- AddressId INT FOREIGN KEY REFERENCES Addresses(Id)
- )
- --Problem 18. Basic Insert
- INSERT INTO Towns([Name])
- VALUES ('Sofia'),
- ('Plovdiv'),
- ('Varna'),
- ('Burgas')
- INSERT INTO Departments([Name])
- VALUES ('Software Development'),
- ('Engineering'),
- ('Quality Assurance'),
- ('Sales'),
- ('Marketing')
- INSERT INTO Employees
- (FirstName, MiddleName, LastName,
- JobTitle, DepartmentId, HireDate,
- Salary, AddressId)
- VALUES ('Ivan', 'Ivanov', 'Ivanov', '.NET Developer', 1, '2013-02-01', 3500.00, NULL),
- ('Petar', 'Petrov', 'Petrov', 'Senior Engineer', 2, '2004-03-02', 4000.00, NULL),
- ('Maria', 'Petrova', 'Ivanova', 'Intern', 3, '2016-08-28', 525.25, NULL),
- ('Georgi', 'Teziev', 'Ivanov', 'CEO', 4, '2007-12-09', 3000.00, NULL),
- ('Peter', 'Pan', 'Pan', 'Intern', 5, '2016-08-28', 599.88, NULL)
- --Problem 19. Basic Select All Fields
- SELECT * FROM Towns
- SELECT * FROM Departments
- SELECT * FROM Employees
- --Problem 20. Basic Select All Fields and Order Them
- SELECT * FROM Towns
- ORDER BY [Name]
- SELECT * FROM Departments
- ORDER BY [Name]
- SELECT * FROM Employees
- ORDER BY Salary DESC
- --Problem 21. Basic Select Some Fields
- SELECT [Name] FROM Towns
- ORDER BY [Name]
- SELECT [Name] FROM Departments
- ORDER BY [Name]
- SELECT FirstName, LastName, JobTitle, Salary FROM Employees
- ORDER BY Salary DESC
- --Problem 22. Increase Employees Salary
- UPDATE Employees
- SET Salary += 0.10 * Salary
- SELECT Salary FROM Employees
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement