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. When the customer returns to the store, they’ll bring the movies they rented. If a movie is lost, the customer should be charged 5 times the daily rental rate of the movie. The cashier should mark the movie as lost and this will reduce the stock. There is no need to keep track of the lost movies. All we need to know is the number of movies in stock and how much the customer was charged. For other movies, the customer should be charged based on the number of days and the daily rental rate. We issue discount coupons from time to time. The customer can bring a coupon when returning the movies. A customer may return the movies they’ve rented on multiple visits. We need to be able to track the - top movies - top customers - revenue per day, month, and year.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
You have been hired at Selianord Group (Pty)Ltd as a Junior
Step by step
Solved in 3 steps
The store manager should be able to add/update/delete the list of movies
Cashiers should have a read-only view of the list of movies
We need to be able to track the - top movies - top customers - revenue per day, month, and year.