New to SQL

1

Can somebody please tell me where I am going wrong?

REM Dropping the table if it exists
drop table hotel cascade constraint;

Create Table Hotel(
HotelId Char(9) Constraint Hotelid_pk Primary Key,
Name VarChar2(45),
Address VarChar2(45),
City VarChar2(20),
State Char(2),
Zip Char(5)
);

drop table Customer cascade constraint;
Create Table Customer(
CustId Char(9) Constraint customrid_pk Primary Key,
Fname VarChar2(15),
Lname Varchar(25),
CreditLimit Number (6,2),
HotelId Char(9),
Constraint CustomerId_FK Foreign Key (CustId) references Customer (CustId)
);

REM INSERTING into Customer

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223467','John','Smith',1000.00,'1049');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223468','Mary','Jones',800.00,'1134');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223469','Steve', 'Evans',600.00,'1075');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223470','Jane', 'Watson',1200.00,'1395');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223471','Bob', 'Stevens',400.00,'2431');

REM INSERTING into Hotel

INSERT INTO Hotel (HotelId, Name, Address, City, State, Zip)
VALUES ('1134','Peachtree Hotel', '245 Peachtree Ave', 'Atlanta','GA','30302');

INSERT INTO Hotel (HotelId, Name, Address, City, State, Zip)
VALUES ('1075','Clemson Inn', '614 Clemson Blvd','Clemson','SC','29619');

INSERT INTO Hotel (HotelId, Name, Address, City, State, Zip)
VALUES ('2431','Disney Beach Resort', '423 Disney Parkway','Orlando','FL','32821');

REM Select
Select * from Hotel;
Select * from Customer;

Select CustID, Fname, Lname, Creditlimit, HotelId
From Customer;
Where (Customer.CustId=Hotel.HotelId)
Order by Lname;

Select HotelId, Name, Count, Customer.CustID (CustomerId) As number of Customer
From Hotel;
Where (Hotel.HotelId=Customer.CustId)
Group By Hotel.HotelId, Name;
Order by Hotel.HotelId, Name;

2

I see so many syntax errors and constructs that are foreign to Microsoft SQL Server. Are you using SQL Server, or are you using another RDBMS?

Assuming it is SQL Server

Create Table Hotel(
HotelId Char(9) Constraint Hotelid_pk Primary Key,
Name varchar(45),
Address varchar(45),
City varchar(20),
State Char(2),
Zip Char(5) 
);

--drop table Customer cascade constraint;
Create Table Customer(
CustId Char(9) Constraint customrid_pk Primary Key,
Fname varchar(15),
Lname Varchar(25),
CreditLimit decimal (6,2),
HotelId Char(9),
Constraint CustomerId_FK Foreign Key (CustId) references Customer (CustId)
);

--REM INSERTING into Customer

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223467','John','Smith',1000.00,'1049');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223468','Mary','Jones',800.00,'1134');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223469','Steve', 'Evans',600.00,'1075');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223470','Jane', 'Watson',1200.00,'1395');

INSERT INTO Customer (CustId, FName, LName, CreditLimit, HotelId)
VALUES ('1223471','Bob', 'Stevens',400.00,'2431');

--REM INSERTING into Hotel

INSERT INTO Hotel (HotelId, Name, Address, City, State, Zip)
VALUES ('1134','Peachtree Hotel', '245 Peachtree Ave', 'Atlanta','GA','30302');

INSERT INTO Hotel (HotelId, Name, Address, City, State, Zip)
VALUES ('1075','Clemson Inn', '614 Clemson Blvd','Clemson','SC','29619');

INSERT INTO Hotel (HotelId, Name, Address, City, State, Zip)
VALUES ('2431','Disney Beach Resort', '423 Disney Parkway','Orlando','FL','32821');

--REM 

Select * from Hotel;
Select * from Customer;

I have not attempted to fix your last two queries - not sure of the logic you are trying to implement.

3

Thanks for helping. It is SQL Developer that I trying to teach myself for work (so I can sort medical info). With the queries I am trying to learn how to sort them by last name, then by credit limit and last how many people are in each hotel. Thanks again!!!

4

That is Oracle, if you are sticking to that, you will be get better help at a oracle forum. SQLTeam is on Microsoft SQL Server