locked
How to create tables using below statement RRS feed

  • Question

  • User1242168447 posted

    Working on small ASP.Net MVC project, and got requirement from business analyst who gave me PDF file with below SQL staff to create in database.

    Looking on how to achieve result from the below SQL question.By the way should all be created using T-SQL. 

    Student - Table

    studentNo Identity column INT NOT NULL PK 

    lastName VARCHAR(30) NOT NULL 

    firstName VARCHAR(30) NOT NULL

    gender CHAR NOT NULL

    phoneNumber VARCHAR(20) NOT NULL

    salaryAmount INT,

    departmentID INT FK 

    Department - Table 

    departmentID Identity Column INT NOT NULL PK

    name VARCHAR(40) NOT NULL

    priceCentreID INT FK 

    PriceCentre - Table 

    priceCentreID 

    name VARCHAR(50)

    accountID varchar(50)

    Tuesday, June 2, 2020 9:14 PM

Answers

  • User1686398519 posted

    Hi,  LetMeCode

    To create a table using T-SQL, please refer to the following.

    Note: Because PriceCentre is the table associated with the foreign key priceCentreID, PriceCentre needs to have a primary key.

    CREATE TABLE [dbo].[PriceCentre] (
        [priceCentreID]   INT NOT NULL PRIMARY KEY ,
        [name] VARCHAR(50),
        [accountID] VARCHAR(50)
    );
    CREATE TABLE [dbo].[Department] (
        [departmentID]   INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
        [name] VARCHAR(40) NOT NULL,
        [priceCentreID] INT FOREIGN KEY REFERENCES PriceCentre(priceCentreID),
    );
    CREATE TABLE [dbo].[Student] (
        [studentNo]   INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
        [lastName] VARCHAR(30) NOT NULL,
        [firstName] VARCHAR(30) NOT NULL,
        [gender] CHAR NOT NULL,
        [phoneNumber] VARCHAR(20) NOT NULL,
        [salaryAmount] INT,
        [departmentID] INT FOREIGN KEY REFERENCES Department(departmentID)
    );

    Here is the result.

     
    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 3, 2020 7:25 AM

All replies

  • User1120430333 posted

    It's DBA 101 any good software developer should know. Maybe,  someone will have a good tutorial for you to start on..

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Wednesday, June 3, 2020 3:47 AM
  • User1686398519 posted

    Hi,  LetMeCode

    To create a table using T-SQL, please refer to the following.

    Note: Because PriceCentre is the table associated with the foreign key priceCentreID, PriceCentre needs to have a primary key.

    CREATE TABLE [dbo].[PriceCentre] (
        [priceCentreID]   INT NOT NULL PRIMARY KEY ,
        [name] VARCHAR(50),
        [accountID] VARCHAR(50)
    );
    CREATE TABLE [dbo].[Department] (
        [departmentID]   INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
        [name] VARCHAR(40) NOT NULL,
        [priceCentreID] INT FOREIGN KEY REFERENCES PriceCentre(priceCentreID),
    );
    CREATE TABLE [dbo].[Student] (
        [studentNo]   INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
        [lastName] VARCHAR(30) NOT NULL,
        [firstName] VARCHAR(30) NOT NULL,
        [gender] CHAR NOT NULL,
        [phoneNumber] VARCHAR(20) NOT NULL,
        [salaryAmount] INT,
        [departmentID] INT FOREIGN KEY REFERENCES Department(departmentID)
    );

    Here is the result.

     
    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 3, 2020 7:25 AM
  • User1242168447 posted

    Thanks Few questions

    Why did you add [dbo] before the table name?

    Also Why did you put the [ ] ?

    and why you use IDENTITY (1, 1) in departmentID and studentID

    I'm asking as I tried to do it without [] and [dbo], it's worked

    Besides, Can you please suggest me a good T-SQL tutorial ?

    Wednesday, June 3, 2020 10:31 AM
  • User1686398519 posted

    Hi,  LetMeCode

    • "dbo" is the default schema in SQL Server. You can create your own schema, and then create tables and views in the new schema. Of course, you can choose not to do this.
    • "[]" is usually used in the field name,table name, database name, because some names such as date which has a special meaning in SQL, so using "[]" to wrap up can avoid SQL understanding errors.
    • Use "use IDENTITY (1, 1)" in order to create an identity column in a table.This link can help you.
    • You can refer to this link for the study of T-SQL.

    Best Regards,

    YihuiSun

    Thursday, June 4, 2020 7:46 AM