Lab 1

Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”, and “LikedAndDisliked”. Each table should consist of five columns that are relatable to the table name. Insert at least five row of data in each table. The data can be any information as long as it is related to the table name.

MC 2 . Write an Anonymous PL/SQL block that will insert five records into the database; the records must include the "jpeg" binary files that contains the photographs associated with each record. One technique would be,   Create BFILE locators for each of the image files   Create 5 INSERT statements into the table   CREATE TABLE StudentRecord ( FirstName VARCHAR2 (20) NOT NULL, LastName VARCHAR2 (40) NOT NULL, HomeworkAvg NUMBER(10,2), AttendanceAvg NUMBER (20,2), ExamAvg NUMBER (10,2), StudentID VARCHAR2 (10),

You have been hired at Selianord Group (Pty)Ltd as a Junior database developer where you are going to build a desktop application called LusuFlix. This application will be used at a video rental store. We need different levels of permissions for different users in the database.  The store manager should be able to add/update/delete the list of movies. They will be in charge of setting the stock for each movie as well as the daily rental rate.  Cashiers should have a read-only view of the list of movies. They should be able to manage the list of customers and the movies they rent. At check out, a customer brings one or more movies. The cashier looks up a customer by their phone number. If the customer is a first-time customer, the cashier asks for their full name, email, and phone number, and then registers them in the system. The cashier then scans the movies the customer has brought to check out and records them in the system.  Each movie has a 10-digit barcode printed on the cover.…

In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text.  Here is the table structure: CREATE TABLE USER_ (   USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL,       PRIMARY KEY (USER_ID) );  CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT (   SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL,      PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR (   SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…

In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text.  Here is the table structure: CREATE TABLE USER_ (   USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL,       PRIMARY KEY (USER_ID) );  CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT (   SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL,      PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR (   SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…

Solve all parts with explanation and Don't use ai to answer

Task 6: The StayWell Property Management team considers creating a discount scheme for the property owners with more than one property in the system. You will need to provide all the owner IDs (OWNER_NUM) and the count of properties of the owners with more than one active property in the database. The output columns should be OWNER_NUM and COUNT(*) respectively.

Chapter 17 How to manage database security Exercises using the My Guitar Shop Database Use Microsoft SQL Server Write a script that creates a user-defined database role named OrderEntry in the MyGuitarShop database. Give INSERT and UPDATE permission to the new role for the Orders and OrderItems table. Give SELECT permission for all user tables. 2.Write a script that (1) creates a login ID named “RobertHalliday” with the password “HelloBob”; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named “RobertHalliday” for the login; and (4) assigns the user to the OrderEntry role you created in exercise 1.

C# using Dapper DB Multimapping Step 1 is done already you must proceed to step 2 See attached photo for the problem and instructions. All the instructions are there already

awk is similar to the SQL language in that you can query certain records. What command below will search the the file /etc/passwd file by locating all users that have a default shell '/bin/bash' and print the first column (which is the user name). awk -D: '$7 == "/bin/bash" {print $3}' /etc/passwd Give this to your new intern to figure out. awk -F: '$4 == "/bin/bash" {print $10}' /etc/passwd awk -F: '$7 == "/bin/bash" {print $1}' /etc/passwd awk -F: '$1 == "/bin/bash" {print $7}' /etc/passwd.

SQL Queries Specify the following SQLite queries on the chinook database.  A schema for the database is in the picture. You can access the database online through your web browser at the SQLite Tutorial, or you can download the chinook.db database from the tutorial site and run it on a labnet machine or your own machine.  SQLite is already installed on labnet machines. You must submit typed queries, not handwritten ones, and save them in a .pdf document.  There is no need to submit the output (results). Each query is worth 1 mark, but some are more difficult than others.   Queries to Specify 5. List the customer id, last name and email address of all customers with a Yahoo or Hotmail email address. 6. List the album id and number of tracks for all albums with 15, 16 or 17 tracks, in descending order of the number of tracks. 7. Find the maximum track length in milliseconds. 8. List the last name, first name and hire date of all employees born in 1961 to 1969 inclusive 9. List the…

SQL Queries Specify the following SQLite queries on the chinook database.  A schema for the database is in the picture. You can access the database online through your web browser at the SQLite Tutorial, or you can download the chinook.db database from the tutorial site and run it on a labnet machine or your own machine.  SQLite is already installed on labnet machines. You must submit typed queries, not handwritten ones, and save them in a .pdf document.  There is no need to submit the output (results). Each query is worth 1 mark, but some are more difficult than others.   Queries to Specify 1. List the last name, postal code and country for all customers from Germany. 2. List the city and number of customers from each city. 3. List the customer id, last name and city for the first 20 customers, when they are ordered by last name ascending. 4. List the customer id, last name and company for all customers who have any company details recorded (the company attribute is not "null").…

In MongoDB, if documents in the collection had key names of fname, lname, age, email, and gender, in addition to the unique identifier, which values would be displayed in the output if the following command was entered: > db.collectionname.find({ "age" : { "$gt" : 18 }}, {fname: 1, lname:1, age:1})   A. fname, lname, age, email, gender, _id   B. fname, lname and age   C. fname, lname, age, email, gender   D. fname, lname, age and _id

Population Database   Compile and run CreateCityDB.java which will create a Java DB database named CityDB. The CityDB database will have a table named City, with the following columns: CityName & Population. Their Data Types: CHAR(50) Primary Key & DOUBLE. The CityName column stores the name of a city, and the Population column stores the population of that city. After you run the CreateCityDB.java program, the City table will contain 20 rows with various cities and their populations. Next, create an application that connects to the CityDB database and allows the user to select any of the following operations: Sort the list of cities by population, in ascending order. Sort the list of cities by population, in descending order. Sort the list of cities by name. Get the total population of all the cities. Get the average population of all the cities. Get the highest population. Get the lowest population.   CreateCityDB.java import java.sql.*; /**  This program creates the CityDB…

Consider a database with the following tables: Patient(PatientID. PatientName, NumberOfVisits) / Doctor(DoctorID. DoctorName) Appointment(AppID. DoctoriD, PatientID. Date, time) 1.Write SQL statement to execute the view AppointmentsView

in Mongo DB We want to add some students to our collection, but this time, we define the value for the _id field. (If the _id is not defined in your document, it will be added automatically.) _id: 1001 first_name: Sarah last_name: Stone email: s_stone@email.com city: Toronto status: full-time gpa: 3.4 program: CPA _id: 1002 first_name: Jack last_name: Adam email: j_adam@email.com city: North York status: part-time gpa: 3.6 program: CPA To add these students, we want to store these documents into a variable first. Define a variable named starray and add these two document to the variable. (You are storing more than one document so you need to define an array.) write the command to define the array: Now, use the starray array to insert the documents to your collection student. Write your insert statement:

Defintion: What is the purpose of a database audit log? SQL Server uses Extended Events in a way that can be summarised in less than 60 words?

In this exercise, you will create tablespaces and change their characteristics.1. Connect to the database as user SYSTEM.2. Create a tablespace (MYTBSP_YourName) in a suitable directory—anydirectory on which the Oracle owner has write permission will do:3. Create a table in the new tablespace, and determine the size of the firstextent:4. Add extents manually, and observe the size of each new extent byrepeatedly executing this command,5. Take the tablespace offline, Try to insert some data in to the table, try todelete data from table, observe the effect, and bring it back online. 6. Drop the tablespace, and use an operating system command to confirmthat the file has indeed gone:

Q1. Use the DBCA to Create a Database In this exercise, you will create a database listener (if one does not exist already) and then create a database to be called MYDB_<your name> using the DBCA, on either Windows or Linux. There is no significant difference between platforms. The illustrations that follow happen to be from Windows.

An objective of this task is to implement your own simple method of auditing the database activities.It is strongly recommended to connect to MySQL either through command line interface mysql or graphical user interface MySQL Workbench and process a script file dbdrop.sql and immediately after that the scripts dbcreate.sql and dbload.sql to refresh a sample database csit115.Download a file solution3.sql and insert into the file the implementations of the following actions.You must connect as a user root with a password csit115.Your implementation must directly follow a comment with a specification of a subtask.(1) First, the script makes a relational table that contains a general log empty.(2) Next, the script sets the appropriate values of the variables to save a general log in a relational table and to start recording a general log from now.(3) Next, the script makes a database csit115 a default database, it stops recording a report, it executes a script file updates.sql, and it…

Oracle Database 18c XE comes with a sample database user named HR. This user owns several database tables in a sample schema for a fictional Human Resources department. However, for security reasons, this user's account is locked. You need to unlock this account before you can view the HR objects or build any applications that use these objects. Assume that you are logged in as Systems Administrator, provide the SQL*plus commands for unlocking the HR account, making sure that the password reuse and life time is unlimited.

Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation. Task: Create a TRIGGER to run on the USERS table before any UPDATE operation.   (SQL Database Test): Create a TRIGGER before any UPDATE operations on the USERS table

Create a CRUD API to manage recipes. Design the database and create your createDB.sql file. Seed your database with some initial records. Your application should store these recipe fields: id name ingredient_1 ingredient_2 ingredient_3 recipe_author_first_name recipe_author_last_name recipe_category_name Follow RESTful conventions to set up the five standard routes for a CRUD api. Make sure your API responds appropriately to various situations: Errors Invalid requests Etc.

Open MS Access, create a database named “orderManagement” and perform the following tasks: 1. Create a table named “Customers” having the following structure. Field Name Date type CustomerID Auto Number customerFName Text customerLName Text customerGender Lookup Wizard (Only Two values “Male”, “Female”) customerAddress Text customerCountry Text customerPhone Text Notes ✓ Set the field CustomerID as the primary key. ✓ Set the Field Size field property of the fields customerFName and customerLName to 50.

You have a list of all your contacts details that you would like to store in a database. Your goal is that when a record is inserted in contacts table, you also want this record to be stored (inserted) in a backup table. Assume you have the two tables created: CONTACTS and CONTACTS_BACKUP. These two (2) tables have the same records. The CONTACTS table have the following columns: CONTACT_ID (Primary_Key), FIRSTNAME, LASTNAME and MOBILE_NO. CONTACTS_ID FIRSTNAME LASTNAME MOBILE_NO 1 100 Princess Kim 813023895 2 101 Henry Packer 811257891 3 102 Petrus Johannes 855698712 4 103 Lukas Shilongo 832584691 The CONTACTS_BACKUP table have the same columns and records as the CONTACTS table. CONTACTS_ID FIRSTNAME LASTNAME MOBILE_NO 100 Princess Kim 813023895 2 101 Henry Packer 811257891 3 102 Petrus Johannes 855698712 4 103 Lukas Shilongo 832584691 You now need to keep both tables in sync so that when a record is inserted into the CONTACTS table it should automatically be inserted into…

Create a MYSQL Database named 'db'Create a MYSQL User named 'dbuser' with a password 'dbpass' Grant Insert, Delete, Update and Select privileges for 'dbuser' on 'db' Create a MYSQL Table on database 'db' and name it as 'students' with the following field and field propertiesField Name Field Type Field Size Additional İnformationStudent No Varchar 6 Primary keyName Varchar 20 Surname Varchar 25 birthdate date

PUT DB ALSO RUN ALSO INNSQL DATABASE ATTACH SCREEN SHOT OF RUNNING DB ELSE DIALIKE.

EventHelpers is a company that specializes in event medical assistance. They can hande any type of medical emergency. The company has a range of employees from first aiders to paramedics with degrees. EventHelpers has contacted you to help them design the database so that they can keep track of which employees went to which events. The company provided you with the following details: • Afirst aider has a certificate, which indicates the expiration date of the certificate. The certificate has to be renewed every three (3) years. • A paramedic has a degree that does not expire. • The company needs the ID, Name, Sumame, Contact details, and the address of each employee. • The company sends several medical assistants, a few first aiders and, depending on the size of the events, 1-3 ambulances, to each event. • Each medical assistant is paid per hour based on their qualificafion. To book EventHelpers for an event, the following details are required: • The name of the event. • The time and…

In this assignment you will write an SQL script that will create a database with five tables.  In addition, you will use insert statements to add some initial records to the tables. Important: You should test your script periodically by running the source command (don’t forget to save it first). Make sure it is running properly before submission. Instructions You have been chosen to create a database for the NFL (National Football League). You will be constructing five tables and loading a small amount of records into each of them.  Your SQL statements will involve DDL (Data Definition Language) syntax, including data types, domain constraints, primary and foreign key designations, among others. Here are the relations you will use as input to build your tables: TEAM(teamID, name, city, state)   COACH(first, last, yearsExperience, currentTeam)              FK: currentTeam -> TEAM   PLAYER(number, team, first, last, hometown, position)              FK: team -> TEAM   GAME(team1,…

After getting a job going on a database, you can use either a trigger or a process. When should a gun be pulled? What are they?