Use an INSERT INTO statement with a subquery containing a SQL string function to create user names for the volunteers based on their legal names and insert them into the table.
+----------------------+
| Tables_in_volunteers |
+----------------------+
|
address
|
|
email
|
|
funds
|
|
hours
|
|
person
|
|
phone
|
|
users
|
+----------------------+
users legal names are in the address table
user table is :
CREATE TABLE USERS(volunteer_id INT NOT NULL, username VARCHAR(50), PRIMARY KEY (username), FOREIGN KEY (volunteer_id) REFERENCES PERSON(volunteer_id));
address table is
CREATE TABLE ADDRESS(name VARCHAR(255), volunteer_id INT NOT NULL, address VARCHAR(255), sex TEXT, age INT, date_joined DATE, PRIMARY KEY (name,date_joined), FOREIGN KEY (volunteer_id) REFERENCES PERSON(volunteer_id));
The query is given as:
INSERT INTO USERS (volunteer_id, username)
SELECT volunteer_id, name from ADDRESS;
Use an INSERT INTO statement with a subquery containing a SQL string function to create user...
Define an SQL view JokesNum that gives the number of jokes each user posts on each day. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...
Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...
Help In Database: Next comes the data entry: Make up data for your database, or find real data. You can enter the data with INSERT statements, but it is much easier to use PhpAdmin. It has a form where you can type the data directly. Alternatively, you can use PhpAdmin to import CSV data (comma-separated values), which you can export from Excel. Include at least 3 rows in each of the entity relations, and as many rows as you need...
Given the mySQL tables created below... Create a mySQL solution to return the itemIDs of items posted by user X, such that all the reviews are “Excellent” or “Good” for these items CREATE TABLE Users ( userId varchar (30) NOT NULL, pass varchar (30), fname varchar (50), lname varchar (50), email varchar (50), gender char(1), age integer, banned boolean, PRIMARY KEY (userId), UNIQUE(email)) CREATE TABLE FavSellers ( userId...
CREATE TABLE Users ( userId varchar (30) NOT NULL, pass varchar (30), fname varchar (50), lname varchar (50), email varchar (50), gender char(1), age integer, banned boolean, PRIMARY KEY (userId), UNIQUE(email)) CREATE TABLE FavSellers ( userId varchar (30), sellerId varchar (30), PRIMARY KEY (userId, sellerId), FOREIGN KEY(userId) references Users, FOREIGN KEY(sellerId) references Users(userId)) CREATE TABLE Items ( itemId integer, title varchar (50), ...
CREATE TABLE Gender ( gender CHAR(1), description VARCHAR(10), PRIMARY KEY (gender) ); CREATE TABLE People ( ID INT, name VARCHAR(50), gender CHAR(1), height FLOAT, PRIMARY KEY (ID), FOREIGN KEY (gender) REFERENCES Gender (gender) ); CREATE TABLE Sports ( ID INT, name VARCHAR(50), record FLOAT, PRIMARY KEY (ID), UNIQUE (name) ); CREATE TABLE Competitions ( ID INT, place VARCHAR(50), held DATE, PRIMARY KEY (ID) ); CREATE TABLE Results ( peopleID INT NOT NULL, competitionID INT NOT NULL, sportID INT NOT NULL,...
Create a new database and execute the code below in SQL Server’s query window to create the database tables. CREATE TABLE PhysicianSpecialties (SpecialtyID integer, SpecialtyName varchar(50), CONSTRAINT PK_PhysicianSpecialties PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (ZipCode varchar(10), City varchar(50), State varchar(2), CONSTRAINT PK_ZipCodes PRIMARY KEY (ZipCode)) go CREATE TABLE PhysicianPractices (PracticeID integer, PracticeName varchar(50), Address_Line1 varchar(50), Address_Line2 varchar(50), ZipCode varchar(10), Phone varchar(14), Fax varchar(14), WebsiteURL varchar(50), CONSTRAINT PK_PhysicianPractices PRIMARY KEY (PracticeID), CONSTRAINT FK_PhysicianPractices_ZipCodes FOREIGN KEY (ZipCode) REFERENCES Zipcodes) go CREATE...
The following SQL DDL script creates a database for a social network application. create table userProfile( id char(10) primary key, firstName varchar(20), lastName varchar(20), dob date, email varchar(30) ); create table foaf( userid char(10), friendID char(10), timeEstablished date, constraint pk primary key(userid, friendID), constraint fk1 foreign key(userid) references userProfile(id), constraint fk2 foreign key(friendID) references userProfile(id) ); create table activity( actID char(20) primary key, topic varchar(20), description varchar(100), location varchar(20), ActivityDate date, hostUser char(10), foreign key(hostUser) references userProfile(id) ); create table...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...