First of all, I created a database and then created and filled tables. After that I answered 3 questions:
- For a student who has a student number 123, show the details of the department where he or she is studying.
- You can see my answer on line number 156.
- Write a 'select' that adds a score to each student's grade in each lesson.
- You can see my answer on line number 162.
- Write a query to display students who have not taken the DB course.
- You can see my answer on line number 166.
In this lab, I used the AdventureWorks2012 database and answered 5 questions:
- For the AdventureWorks2012, write a query that returns the general information of the orders being processed, which are between 100000 and 500000, and the place of order is from France or one of the countries in the North American region.
- You can see my answer on line number 14.
- For the AdventureWorks2012, write a query that shows the sales order identification number, customer identification number, order amount(TotalDue), order date, and the name of the region where the order was registered, for each order.
- You can see my answer on line number 22.
- For the AdventureWorks2012, write a query that shows which area had the most orders for each product.
- You can see my answer on line number 29.
- You can see the answer (version 2) on line number 47.
- For the AdventureWorks2012, create a table called NAmerica_Sales, which is in accordance with question number 1. Add a column to this table and add a constraint to have only Low, High, or Mid values. Then update this column for each tuple to 'High' when the order cost(TotalDue) is higher than the average total cost (The cost of all orders) on the North American region. Update the column to 'Mid' if the order cost(TotalDue) is equal to the average total cost on the North American region. Finally, update the column to 'Low' when the order cost(TotalDue) is lower than the average total cost on the North American region.
- You can see my answer on line number 72.
- increase all employee salaries for each hour of work by 20%, 15%, 10% or 5%. Then give them a level in accordance with their salaries.
- You can see my answer on line number 105.
In this lab, I worked with "User" and "Login" in the SQL Server.
- SQL "Login" is for Authentication:
- Authentication can decide if we have permission to access the SERVER or not.
- A "Login" grants the principal entry into the SERVER.
- SQL server "User" is for Authorization:
- Authorization decides what are different operations we can do in a DATABASE.
- A "User" grants a login entry into a single DATABASE.
I answered these questions:
- First, create a "Login" in the SQL Server.
- You can see my answer on line number 1 in the sa_admin.sql file.
- Create a role that has the ability to manage the database.
- You can see my answer on line number 5 in the sa_admin.sql file.
- Connect the role to the "Login" created in the first question.
- You can see my answer on line number 14 in the sa_admin.sql file.
- With the "login" created in Question 1, log in to the SQL server and then create a table in the AdventureWorks2012 database, Insert data in this table, and select data from it.
- You can see my answer on line number 17 in the sa_admin.sql file and line number 1 in the myLogin.sql file.
- In the AdventureWorks2012 database, create a role called it role2 that has no access to the tables but can only manage permissions.
- You can see my answer on line number 26 in the sa_admin.sql file.
- Grant DataReader permission to role 2.
- You can see my answer on line number 36 in the sa_admin.sql file.
In this lab, I used the AdventureWorks2012 database and answered 2 questions:
- For the AdventureWorks2012, write a query that has the number of orders and the total value of the orders for each Territory along with its Territory area. Show the total number and value of orders for all Territories in one area and at the end of the report for all areas.
- The output should be the same as the following image:
- You can see my answer on line number 1.
- You can see the answer (version 2) on line number 32.
- For the AdventureWorks2012, write a query that shows the number of orders and the total value of orders for each subcategory of goods along with its category. Show the number and value of orders for all subcategories in one category and at the end of the report for all categories.
- The output should be the same as the following image:
- You can see my answer on line number 71.
- You can see the answer (version 2) on line number 121.
In this lab, I used the AdventureWorks2012 database and answered 5 questions:
- For the AdventureWorks2012, write a query that shows all the products' names along with the number of sales of each item (Order Qty) in each region as a separate column for each product.
- You can see my answer on line number 1.
- For the AdventureWorks2012, modify the following code, to have the output as shown below:
select Person.BusinessEntityID, PersonType, Gender from Person.Person join HumanResources.Employee on (Person.BusinessEntityID = Employee.BusinessEntityID)
- You can see my answer on line number 21.
- For the AdventureWorks2012, write a query that returns all the products’ names which names are less than 11 characters long and which the 2 left character of name to the end is 'e'. (Like Freewheel)
- You can see my answer on line number 37.
- For the AdventureWorks2012, write a function that gives an 11 character word as input, and if the form of the input is the same as YYYY/MM/DD (Like 2019/09/17) from, it returns 'September 17 2019' otherwise it returns 'Wrong format input'
- You can see my answer on line number 44.
- For the AdventureWorks2012, write a function that gives three inputs for the year, month, and the products’ names, and returns territories that have sold the product at least once on the desired date.
- You can see my answer on line number 110.
Reach out to me at riasiarman@yahoo.com