Your security team has brought a request to you. There’s been a breach in the customer database, and certain customer passwords need to be reset.
Question
Your security team has brought a request to you. There’s been a breach in the customer database, and certain customer passwords need to be reset. The initial criteria are any customer whose last name is between A-M. And the first digit of the password is a number. So For this implementation, break the solution it into two parts. Write the SQL commands to accomplish this.
Part A: Create a copy of the Customers table (with CustomerID, Password fields) for Customers whose last name is between A-M. So-called CustomersAM with an additional column called resetpwd to store the state of whether or not the password should be reset. The value of resetpwd is either ‘YES’, or ‘NO’
Part B: Now create an update statement(s) to update resetpwd to ‘YES’ for all Customers whose first character of the password is a number, and ‘NO’ for the rest In order to capture the first character of the password use the LEFT() function (Links to an external site.) which is explained in the book or online documentation.
Write the SQL commands to accomplish this.
Explanation
SQL stands for the structure query language. And this language is used to store data in a particular place. So that user can find the data if it is lost.
This language has different commands to execute. Such as:
-
- SELECT: This command is used to select the data.
- CREATE: This command is used to create different objects in SQL. Like tables, rows, columns etc.
- INSERT: This command is use to insert the values in the tables, rows, columns.
- UPDATE: This command is user to update the data in the program.
There are different keys in the SQL. Like primary key, foreign keys and unique keys. Also Rows are also known as tuple in the SQL. Where as columns are called as attribute. All the data in SQL is always store in the form of tables.
SQL Commands for this are:
⦁
CREATE TABLE Customers (CustomersID char[3], F_Name varchar[8],L_Name varchar[8],Password varchar[10]);
Insert into Customers VALUES('C01',"Rose","Maria","1Maria");
Insert into Customers VALUES('C05',"Joanna","Singh","1Maria");
Insert into Customers VALUES('C07',"David","Baker","Baker34");
CREATE TABLE CustomersAM AS
Select CustomersID, Password from Customers
where L_Name>='A' and L_Name<'N';
Alter TABLE CustomersAM
ADD column resetpwd char[3] DEFAULT 'NO';
UPDATE CustomersAM
SET resetpwd='YES'
where Password>="0" and Password<="9";
Select *from CustomersAM;
Output
