SHARE
TWEET

Untitled

a guest Jul 22nd, 2019 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Tao co so du lieu
  2. CREATE DATABASE Company ON PRIMARY
  3.     (NAME = 'Company',
  4.     FILENAME = 'D:\Documents\Sen\CSDL\BT\Company.mdf',
  5.     SIZE = 3072KB,
  6.     MAXSIZE = UNLIMITED,
  7.     FILEGROWTH = 1024KB)
  8. LOG ON
  9.     (NAME = 'Company_log',
  10.     FILENAME = 'D:\Documents\Sen\CSDL\BT\Company.Ldf',
  11.     SIZE = 1024KB,
  12.     MAXSIZE = 2048KB,
  13.     FILEGROWTH = 10%)
  14. GO
  15. DROP DATABASE Company
  16. GO
  17.  
  18. USE Company
  19. GO
  20. --Chon bang
  21. SELECT * FROM Employee;
  22. SELECT * FROM Department;
  23. SELECT * FROM DepartmentLocation;
  24. SELECT * FROM Dependent;
  25. SELECT * FROM Project;
  26. SELECT * FROM WorksOn;
  27.  
  28. --Company Query
  29. --1. List the names of employees whose last name begin N character.
  30. SELECT FName, MInit, LName
  31.     FROM Employee
  32.     WHERE LName LIKE 'N%';
  33. /*
  34. RameshKNarayan
  35. */
  36.    
  37. SELECT FName+ ' ' + MInit + ' ' + LName
  38.     FROM Employee
  39.     WHERE LName LIKE 'N%';
  40. /*
  41. Ramesh K Narayan
  42. */
  43.  
  44. --2. List employees whose sex is male and has address in Spring or Humble.
  45. SELECT FName, MInit, LName, Sex, Address
  46.     FROM Employee
  47.     WHERE Sex = 'M' AND Address IN ('Sping, TX','Humble, TX');
  48. /*
  49. Ramesh  K   Narayan M   Humble, TX
  50. */
  51.  
  52. --5. List the names of employees whose birth date is from 1940 to 1960, sex is female and salary is from 30000 to 50000.
  53. SELECT FName, MInit, LName,BDate, Sex, Salary
  54.     FROM Employee
  55.     WHERE Sex = 'M' AND (BDate BETWEEN '1940-01-01' AND '1960-12-31') AND (Salary>=30000 AND Salary<=50000);
  56.      
  57. ---nnn
  58. SELECT FName+ ' ' + MInit + ' ' + LName
  59.     FROM Employee E, WorksOn W, Project P
  60.     WHERE E.SSN = W.ESSN AND W.PNo = P.PNumber AND PName = 'Computerization';
  61. /*
  62. Franklin T Wong
  63. Ahmad V Jabbar
  64. Alicia J Zelaya
  65. */
  66. SELECT FName+ ' ' + MInit + ' ' + LName
  67.     FROM Employee E JOIN WorksOn W ON E.SSN = W.ESSN
  68.         JOIN Project P ON W.PNo = P.PNumber
  69.     WHERE PName = 'Computerization';
  70.    
  71. --3. Retrieve the names of project which does in Houston and managed by department 5.
  72. SELECT PName
  73.     FROM Project P
  74.     WHERE P.PLocation='Houston' AND P.DNum=5
  75.  
  76. --4. List dependents whose relationship is son and birth date is 1988 year
  77. SELECT  *
  78.     FROM Dependent
  79.     WHERE Relationship='Son' AND BDate LIKE '%1978%'
  80.    
  81. --7. Find the names of employees that are directly supervised by 'Franklin Wong'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top