Advertisement
gabi11

SQL - SoftUni Database

Sep 23rd, 2019
250
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.19 KB | None | 0 0
  1. --Problem 16. Create SoftUni Database
  2. CREATE DATABASE SoftUni
  3.  
  4. USE SoftUni
  5.  
  6. CREATE TABLE Towns (
  7.     Id INT PRIMARY KEY IDENTITY,
  8.     [Name] NVARCHAR(30) NOT NULL
  9. )
  10.  
  11. CREATE TABLE Addresses (
  12.     Id INT PRIMARY KEY IDENTITY,
  13.     AddressText NVARCHAR(30) NOT NULL,
  14.     TownId INT FOREIGN KEY REFERENCES Towns(Id)
  15. )
  16.  
  17. CREATE TABLE Departments (
  18.     Id INT PRIMARY KEY IDENTITY,
  19.     [Name] NVARCHAR(30) NOT NULL
  20. )
  21.  
  22. CREATE TABLE Employees (
  23.     Id INT PRIMARY KEY IDENTITY,
  24.     FirstName NVARCHAR(30) NOT NULL,
  25.     MiddleName NVARCHAR(30),
  26.     LastName NVARCHAR(30) NOT NULL,
  27.     JobTitle NVARCHAR(30) NOT NULL,
  28.     DepartmentId INT FOREIGN KEY REFERENCES Departments(Id),
  29.     HireDate DATE NOT NULL,
  30.     Salary DECIMAL(10, 2) NOT NULL,
  31.     AddressId INT FOREIGN KEY REFERENCES Addresses(Id)
  32. )
  33.  
  34. --Problem 18. Basic Insert
  35.  
  36. INSERT INTO Towns([Name])
  37. VALUES      ('Sofia'),
  38.             ('Plovdiv'),
  39.             ('Varna'),
  40.             ('Burgas')
  41.  
  42. INSERT INTO Departments([Name])
  43. VALUES      ('Software Development'),
  44.             ('Engineering'),
  45.             ('Quality Assurance'),
  46.             ('Sales'),
  47.             ('Marketing')
  48.  
  49. INSERT INTO Employees
  50.         (FirstName, MiddleName, LastName,
  51.         JobTitle, DepartmentId, HireDate,
  52.         Salary, AddressId)
  53. VALUES      ('Ivan', 'Ivanov', 'Ivanov', '.NET Developer', 1, '2013-02-01', 3500.00, NULL),
  54.             ('Petar', 'Petrov', 'Petrov', 'Senior Engineer', 2, '2004-03-02', 4000.00, NULL),
  55.             ('Maria', 'Petrova', 'Ivanova', 'Intern', 3, '2016-08-28', 525.25, NULL),
  56.             ('Georgi', 'Teziev', 'Ivanov', 'CEO', 4, '2007-12-09', 3000.00, NULL),
  57.             ('Peter', 'Pan', 'Pan', 'Intern', 5, '2016-08-28', 599.88, NULL)
  58.  
  59. --Problem 19. Basic Select All Fields
  60.  
  61. SELECT * FROM Towns
  62. SELECT * FROM Departments
  63. SELECT * FROM Employees
  64.  
  65. --Problem 20. Basic Select All Fields and Order Them
  66.  
  67. SELECT * FROM Towns
  68. ORDER BY [Name]
  69.  
  70. SELECT * FROM Departments
  71. ORDER BY [Name]
  72.  
  73. SELECT * FROM Employees
  74. ORDER BY Salary DESC
  75.  
  76. --Problem 21. Basic Select Some Fields
  77.  
  78. SELECT [Name] FROM Towns
  79. ORDER BY [Name]
  80.  
  81. SELECT [Name] FROM Departments
  82. ORDER BY [Name]
  83.  
  84. SELECT FirstName, LastName, JobTitle, Salary FROM Employees
  85. ORDER BY Salary DESC
  86.  
  87. --Problem 22. Increase Employees Salary
  88.  
  89. UPDATE Employees
  90. SET Salary += 0.10 * Salary
  91.  
  92. SELECT Salary FROM Employees
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement