Assignment 2 DATABASE (6)

SQL : Write a statement that allows the same classmate to modify (insert, update, and delete) the data of the newEmpXX table

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.

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions. Under what circumstances would you create a View?

The database contains a Horse table, with columns: ID integer, primary key Registered Name variable-length string. The database contains a Student table, with columns: • ID integer, primary key First Name - variable-length string LastName variable-length string Write a SQL Query to create a Schedule table, with columns: HorseID - integer with range 0 to 65535, not NULL • Student ID integer with range 0 to 65535 Lesson DateTime - date/time, not NULL (HorseID, Lesson DateTime) is the primary key Also, create the following foreign key constraints on Schedule columns: HorseID references Horse. When an ID is deleted from Horse matching Lesson Schedule rows are deleted. Student ID references Student. When an ID is deleted from Student, matching Student ID 's are set to NULL).

PL/SQL Help I need to create a PL/SQL trigger called aprove_rental that will prevent an INSERT if a clients rating (client_credit_rtg) is less than 1.5 from the rental table. Im using sqlplus to test this trigger. Tables : create table client (client_num char(4), client_lname varchar2(15) not null, client_fname varchar2(15), client_phone char(8), client_credit_rtg decimal(2,1) check(client_credit_rtg between 0.0 and 5.0), client_fave_cat char(3), primary key (client_num), foreign key (client_fave_cat) references movie_category(category_code) ); create table rental (rental_num char(7), client_num char(4), vid_id char(6), date_out date not null, date_due date not null, date_returned date, primary key (rental_num), foreign key (client_num) references client, foreign key (vid_id) references video ); PLEASE WRITE THIS FROM SCRATCH AND NOT JUST COPY AND PASTE FROM ANOTHER ANSWER, BECAUSE THEY DONT WORK FOR ME

In SQL code: Create a PL/SQL block to retrieve the name of each employee from the EMPLOYEE table and print the first initial of each name on the screen, incorporating an INDEX BY table.

sql

FlugHafenDB_v2 Database (Microsoft SQL) Create a StoredProcedure AddAirline that will prompt the user for all the required fields for the Airline table. Utilize a Try Catch to output a customized error message if the insert statement results in an error.

oracle PL/SQL Create an anonymous block that prints all instructors First Name, Last name, and a phone. Hint: Use cursor and coursor FOR loop.  desc instructor----------------------------------------- -------- ----------------------------INSTRUCTOR_ID     NOT NULL NUMBER(8)SALUTATION   VARCHAR2(5)FIRST_NAME    VARCHAR2(25)LAST_NAME    VARCHAR2(25)STREET_ADDRESS   VARCHAR2(50)ZIP   VARCHAR2(5)PHONE   VARCHAR2(15)

Create a table in your own database using the following statement. CREATE TABLE DateRange (DateID INT IDENTITY, DateValue DATE, DayOfWeek SMALLINT, Week SMALLINT, Month SMALLINT, Quarter SMALLINT, Year SMALLINT ); Write a stored procedure that accepts two parameters: A starting date The number of the consecutive dates beginning with the starting date The stored procedure then inserts data into all columns of the DateRange table according to the two provided parameters.

Sales Database: Customers(custId, lastName, firstName, address, phone, creditLimit) Orders(ordNumber, itemNumber, qtyOrdered.) Items(itemNumber, itemName, price)   For the Sales Database referenced above, write the SQL command to create the LineItem table, assuming the Orders table and items table already exist.

Write a PL/SQL block that will allow the user to enter FNo and that will display Customer Name from Booking table. Include predefined exception with suitable messages1- SQL returns more than one records2- Records are not available

Q4

Write an anonymous block that will display only positive numbers between 1 and 20.With PL/SQL code

1. Create tblEmp with three columns, namely, EID, Name and Salary 2. Create an anonymous PL/SQL block that will accept user input for the following: 1-INSERT NEW RECORD 2-UPDATE A RECORD 3-DELETE RECORD ENTER OPTION: 1 Note: use bind variable to get the option from the user then perform the transaction based on the chosen option. use bind variables for the appropriate columns in the transaction) Run and copy paste the code here.

INFO 2303  Database Programming 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 delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.

Submit Result Key Here.. Subm SQL Injection Escaping Challenge To complete this challenge, you must exploit SQL injection flaw in the following form to find the result key. The developer of this level has attempted to stop SQL Injection attacks by escaping apostrophes so the database interpreter will know not to pay attention to user submitted apostrophes Challenge Hint This is the query you are injecting code into! Be aware that your apostrophes are being escaped with a leading backslash before being sent to the interpreter SELECT FROM customers WHERE customerld =" Please enter the Customer Id of the user that you want to look up Get user There were no results found in your search

assume that a database has a table named Stock, with thefollowing columns:Column Name TypeTrading_Symbol nchar(10)Company_Name nchar(25)Num_Shares intPurchase_Price moneySelling_Price money Write a Select statement that returns all the columns from the rows where Trading_Symbol starts with "SU".

class Database:def __init__(self, db_name):self.db = sqlite3.connect(db_name)self.cursor = self.db.cursor()def create_table(self, table_schema):self.cursor.execute(table_schema)self.db.commit()def insert_data(self, table_name, data):for i in range(len(data)):keys = ", ".join(data.columns)values = ", ".join([f"'{value}'" if pd.isna(value) else str(value) for value in data.iloc[i]])self.cursor.execute(f"INSERT INTO {table_name} ({keys}) VALUES ({values})")self.db.commit()def search_data(self, table_name, year):self.cursor.execute(f"SELECT * FROM {table_name} WHERE year={year}")result = self.cursor.fetchone()if result is not None:return resultelse:return Nonedef delete_data(self, table_name, year):self.cursor.execute(f"DELETE FROM {table_name} WHERE year={year}")self.db.commit()return self.cursor.rowcount > 0   Please rewrite (or convert) the code above with format: def QueryBuilder( Data_Base, Query_Type, Query_Tuple):'''''' Build Query_String'''''' return Query_String

Online shopping system & online book store: Rebuild View/Edit cart and checkout pages using PL/SQL like the following: Create a function/procedure to update the quantity of the product in the cart if the new quantity is available in the store. Create a trigger that deletes the product from the cart after updating the quantity to 0. Don't forget to return the old quantity to the store. Create a trigger that empties the customer cart after the customer clicks on the checkout.

The database has three tables for tracking horse-riding lessons:   Horse with columns: ID - primary key RegisteredName Breed Height BirthDate     Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress     LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key   Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule.

The database has three tables for tracking horse-riding lessons:   Horse with columns: ID - primary key RegisteredName Breed Height BirthDate     Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress     LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key   Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs