write sql function to calculate GPA as seen on my image sent SUBMISSION DATE:INSTRUCTION> Use the Sample Database Given in a File named GPAdatabase.sql to Answer the QuestionQUESTIONGiven the sample of the GPADatabase in a file, write SQL Stored function called calculateGPAwhich receive student's registration number, semester and academic year and return thecorresponding semester GPA truncated to one decimal place.NOTE:GPA = (Grade Points x Credits)Σ CreditsTruncation means to discard the remaining. That is 3.9999 = 3.9 when truncated to onedecimal placeExample of GPA CalculationMODULELTU 07205LTU 07206LTU 07207LTU 07208LTU 07209GPA =CREDITS12151212(4x12) + (2x15)+(4x12)+(2 x 12)+(1x0)12 +15 +12 +12 +0= 2.9NIT/BLTM/2017/214NIT/BLTM/2017/215NIT/BLTM/2017/215NIT/BLTM/2017/216NIT/BLTM/2017/216NIT/BLTM/2017/217NIT/BLTM/2017/217Compare your results against these outputstudentIDNIT/BLTM/2017/214SCORE65.045.864.040.035.7Test your function against the following query, changing the value of the studentID andsemesteraYear2017/20182017/2018select studentID, aYear, semester, calculateGPA(studentID, aYear, semester) from enrollmentwhere studentID=NIT/BLTM/2017/214' AND ayear=2017/2018' AND semester=1 limit 1:2017/20182017/20182017/20182017/20182017/20182017/2018GRADEB+ (4 points)C (2 points)B+ (4 points)C (2 point)D (1 point)Semester12121212=GPA3.42.90.92.83.03.25.04.715051Golden HINT:Use combination of SQL Counter Controlled While loop and SQL if-else-if structures to helpsolve the question

Steps to create a SQL stored function to calculate the semester GPA: Step – 1: Connect to your SQL…

QUESTION Given the sample of the GPADatabase in a file, write SQL Stored function called calculateGPA which receive student's registration number, semester and academic year and return the corresponding semester GPA truncated to one decimal place. NOTE: GPA = (Grade Points x Credits) Σ Credits

QUESTION Given the sample of the GPADatabase in a file, write SQL Stored function called calculateGPA which receive student's registration number, semester and academic year and return the corresponding semester GPA truncated to one decimal place. NOTE: GPA = (Grade Points x Credits) Σ Credits

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

INFO 2303 Database Programing Assignment # : PL/SQL Procedure & Function Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write a function that takes patient date of birth. If the patient born after 30th June 1990 then he/she will be required to set appointment for immunization. Call this function from anonymous block to allow the user to enter the patient ID. Expected output: Enter the patient ID: 168 The status of X-immunization : REQUIRED
Run these program in sql and description errors and print number of errors and correct to error 1- declare v_name student_information.name%type; begin select name into v_name from student_information where id >1; dbms_output.put_line(v_name); end; 2-begin insert into student_information values(4,'majed','ma@a.com',null);end;
SQL QUERIES Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)Department(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)A: Write a query in SQL to display the minimum, maximum salary and DEPARTMENT_ID of all thosedepartments whose minimum salary is greater than 4000 in ascending order. B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table. C: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format: 11-july-2019
Please written by computer source 1. For this project, you need to create the company database and populate it with the data given in the Oracle SQL PLUS environment (if you have not done so) 2. Your SQL commands should be placed in file(s) and run from the file(s). 3. Implement Triggers on all the tables to change character data to uppercase in the character data type columns. 4. Implement a Function that returns Dname from Department for any give employee. The parameter to the function would be the SSN. 5. Implement a Function that returns manager’s full name for any given department. The parameter to the function would be the department name. 6. Implement another Function that returns manager’s full name for any given department. The parameter to the function would be the department number. 7. Implement a Procedure that increases an employee salary by x%. Employee is identified by SSN and the percentage of increase is given as an input. 8. Implement a Package that contains the…
ies in relational algebra. Find the username of users who are from ‘Muscat’. Find the IDs of pictures which scores more than 2000 likes. Find IDs of pictures which descried as landscape. b. Write SQL queries for the following statements. Display all the picture’s details in the decreasing order of their scores. Count and display the number of pictures posted by each user. Add the given new user’s data into the Users table. userId userName userCity 2536 User2021om Muscat
on this table CREATE TABLE students ( std_id int NOT NULL PRIMARY KEY, f_name VARCHAR2(20), l_name VARCHAR2(20), std_address VARCHAR2(50), tel_no VARCHAR2(15), SEX VARCHAR2(10), birthdate date ); use PL/SQL oracle to solve create A Function getAllData(arg): When Your call your function it should search about an item in your table and returns all data about it. arg is int all data are int and varchar2 and date
Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR Verify that insert has been done successfully by issuing a select * from doctor.
Instructor(Instructor_Id, Instructor_Name, specialization, Address,#Department_Id)Department(Department_Id, Department_Name)Course(Course_code, Course_Title,Credit_hours,Contact_hours,#Department_Id)Teaching(#Instructor_Id, #Course_code)a) Express in SQL the following queries:i. Change to 6 the number of contact hours of the course which has thecode “ITDR2104”.ii. Add a new column named instructorLoad of type Number(2) to thetable instructor.iii. List the details of all the courses having a code that starts with“ITDR”. The output should be sorted in descending order by credithours.iv. Find the number of courses taught by the instructor whose name is“Ahmed”.v. List the names of all departments along with the number of coursesoffered by each department.vi. Find the titles of courses, which are taught by the instructor who isnamed “Ahmed”.b) Create a view named instructor_of_DBCourse to contain the details(Instructor_Name, specialization, Address, Department_Id) ofinstructors who teach…
31) Using functions display: a) sum of all salaries b) average of all salaries c) highest salary d) lowest salary 27) Show employees that do not work for IBM, Oracle, AWS? 28) Select all employees where commission_pct has null values? 29) Use NVL command where in commission_pct attribute will convert from null values to 0 30)Use the oracle database employees table and CASE expression to decode the department id. Display the department id, last name, salary and a column called all sql
Answer these question by using these tables. Please. Create in PL/SQL a stored procedure called 'StudentsActivities' to display the list of students and the activities in which they participated. Create in PL/SQL a stored Function called 'Checkavailability' to check whether 'Salalah Hall' is busy or not in a given date. Create in PL/SQL a stored Procedure called 'CheckavailabilityProc' to call the function 'Checkavailability' and display the appropriate message according to the value returned by the function (If function returns true, the procedure will display 'Salalah Hall is free on date ######' otherwise'Salalah Hall is busy on date ###### '). CREATE TABLE Student (studid Char(9) Primary Key,FirstName Varchar2(50) NOT NULL,LastName Varchar2(50) NOT NULL,Email Varchar2(50) NOT NULL UNIQUE,PhoneNumber Number(8) NOT NULL UNIQUE,GPA Number(1,2) NOT NULL); Insert into Student(studid, FirstName, LastName, Email, PhoneNumber, DateOfBirth, GPA) values…
1. Write SQL retrieval commands for each of the following queries:a. Display the course ID and course name for all courses with an MC prefix.b. Display all courses for which Professor Newton has been qualified.c. Display the class roster, including student name, for all students enrolled in section 1003 of MC123 and section 1005 of MC222.2. Write an SQL query to answer the following question: Which instructors are qualified to teach MS211 and MS111?3. Write an SQL query to answer the following question: Is any instructor qualified to teach MS111 and not qualified to teach MS232?4. Write SQL queries to answer the following questions: How many students were enrolled in section 1003 during semester I-2021?5. How many students were enrolled in MS232 during semester I-2021?6. Write an SQL query to answer the following question: Which students were not enrolled in any courses during semester 1-2021?
USE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.