select Product.ProductName, Product.ProductUnitSize, Product.ProductunitPrice fr
ID: 3860401 • Letter: S
Question
select Product.ProductName, Product.ProductUnitSize, Product.ProductunitPrice
from OrderDetail, Product
where OrderDetail.orderKey = 1000 and Product.ProductKey = OrderDetail.ProductKey
List all the order and order details for each order made by the customer with the phone number (206) 555-6623.
select CustomerOrder.OrderDate, CustomerOrder.OrderTime, CustomerOrder.CustomerPhoneKey, Product.ProductName, OrderDetail.OrderDetailQuantity, OrderDetail.OrderDetailPriceChanged
from CustomerOrder, OrderDetail, Product
where CustomerOrder.CustomerPhoneKey = '(206) 555-6623'
and CustomerOrder.OrderKey = OrderDetail.OrderDetailKey
and Product.ProductKey = OrderDetail.ProductKey
Change the price of breadsticks to 3.00.
update Product set ProductUnitPrice = 3.00 where ProductName='Breadsticks'
Process a pizza order for a new customer. (This will involve 3 INSERT statements.)
Insert into Customer('(206) 555-1234', 'L1', 'A1', 'A2', 'C1', 'S1', 93323);
Insert into CustomerOrder(1006, '10/8/2017', '2:15 PM', '32432432432', 'btaylor');
Insert into OrderDetail(11, 1006, 'specialM', 1, 16.25);
Review the pizza database we built in Chapter 6 and queried in Chapter 7.
Complete practice question #2 at the end of chapter 8.
Develop a threat analysis for the pizza company database.
Complete practice question #3 at the end of chapter 8.
Create roles for the various types of users in the pizza database.
Explanation / Answer
Threat analysis for the pizza company database.The pizza company database can have following threats:
Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database). To create a role use the CREATE ROLE SQL command:
name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. (In practice, you will usually want to add additional options, such as LOGIN, to the command. More details appear below.) To remove an existing role, use the analogous DROP ROLE command:
For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:
To determine the set of existing roles, examine the pg_roles system catalog, for example
The psql program's du meta-command is also useful for listing the existing roles.
In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a "superuser", and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.
Every connection to the database server is made in the name of some particular role, and this role determines the initial access privileges for commands issued on that connection. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. For example, the psql program uses the -Ucommand line option to indicate the role to connect as. Many applications assume the name of the current operating system user by default (including createuser and psql). Therefore it is often convenient to maintain a naming correspondence between roles and operating system users.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.