First of all whole question is given I just need out of given queries ASAP!! Write the SQL statements for the following User table. User_id Name City Order_date Purchase_amount 101 Alex Paris 2012-10-05 150.5 102 James London 2012-10-05 270.3 103 Paul Paris 2012-10-05 120.8 104 Lawson Rome 2012-10-05 230.5 105 Kate Beijing 2012-10-05 30.2 Write a query to create a user table containing following columns. Whereas user_id as primary key. Write a query to insert multiple records into user table. Write a query to add two columns, country and currency in user table. Write a query to update user table, set city as shanghai for a user having user_id 105. Write a query to remove all records from user table. Write a query to drop a column purchase _amount from user table. Write a query to display name of user in descending order whose order purchase amount is in between 200 to 270. Write a query to display name of user who belong to either Paris, London or CS Beijing. Write query to display name of an employee who belong to paris. Write a query to display unique city name form user table.
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.
First of all whole question is given I just need out of given queries ASAP!!
Write the SQL statements for the following User table.
User_id
|
Name |
City
|
Order_date
|
Purchase_amount
|
101 |
Alex |
Paris |
2012-10-05 |
150.5 |
102 |
James |
London |
2012-10-05 |
270.3 |
103 |
Paul |
Paris |
2012-10-05 |
120.8 |
104 |
Lawson |
Rome |
2012-10-05 |
230.5 |
105 |
Kate |
Beijing |
2012-10-05 |
30.2 |
- Write a query to create a user table containing following columns. Whereas user_id as primary key.
- Write a query to insert multiple records into user table.
- Write a query to add two columns, country and currency in user table.
- Write a query to update user table, set city as shanghai for a user having user_id 105.
- Write a query to remove all records from user table.
- Write a query to drop a column purchase _amount from user table.
- Write a query to display name of user in descending order whose order purchase amount is in between 200 to 270.
- Write a query to display name of user who belong to either Paris, London or CS Beijing.
- Write query to display name of an employee who belong to paris.
- Write a query to display unique city name form user table.
Answer
1.
create table User (User_id integer PRIMARY KEY, Name varchar(100), City varchar(100), Order_date date, Purchase_amount float);
insert into User (User_id, Name, City, Order_date, Purchase_amount) values (101, 'Alex', 'Paris','2012-10-05',150.5);
insert into User (User_id, Name, City, Order_date, Purchase_amount) values (102, 'James', 'London','2012-10-05',270.3);
insert into User (User_id, Name, City, Order_date, Purchase_amount) values (103, 'Paul', 'Paris','2012-10-05',120.8);
insert into User (User_id, Name, City, Order_date, Purchase_amount) values (104, 'Lawson', 'Rome','2012-10-05',230.5);
insert into User (User_id, Name, City, Order_date, Purchase_amount) values (105, 'Kate', 'Beijing','2012-10-05',30.2);
2.
insert into User (User_id, Name, City, Order_date, Purchase_amount) values (106, 'Jackie', 'London','2012-10-05',150.6);
3.
ALTER TABLE User ADD country varchar(100);
ALTER TABLE User ADD currency varchar(100);
4.
UPDATE User SET City = 'Shanghai' WHERE User_id = 105;
5.
DELETE FROM User;
6.
ALTER TABLE User DROP Purchase_amount;
7.
SELECT Name FROM User WHERE Purchase_amount BETWEEN 200 and 270 ORDER BY Purchase_amount DESC;
8.
SELECT Name FROM User WHERE City = "Paris" OR City ="London" OR City ="Beijing";
9.
SELECT Name FROM User WHERE City = "Paris";
10.
SELECT DISTINCT City FROM User;
Step by step
Solved in 2 steps